Unique filtered index with multiple conditions in SQL Server

Question

Is it possible to create a filtered index in SQL Server with multiple conditions?

Here is what I am trying to do, but gives 'incorrect syntax' error:

CREATE UNIQUE NONCLUSTERED INDEX IX_TestTable  
ON TestTable(MyIntColumn)  
WHERE MyIntColumn is not null OR MyIntColumn<>0

Lianaliane

Yes, you can have multiple conditions -- but OR is not allowed, only AND. Your condition makes little sense because MyIntColumn <> 0 implies MyIntColumn IS NOT NULL. – Dotation

Also, see the documentation learn.microsoft.com/en-us/sql/t-sql/statements/… – Chu

Thank you Jeroen, this is the answer I was looking for. I am new to StackOverflow, and it seems that it is not possible to mark your comment as an answer. What is the correct way to close the topic? – Lianaliane

Answer 1

Use the following syntax:

CREATE UNIQUE NONCLUSTERED INDEX IX_TestTable  
ON TestTable(MyIntColumn)  
WHERE ISNULL(MyIntColumn,0) <> 0

Jackhammer