If you’re getting the “Incorrect syntax…” error in SQL Server when trying to create a filtered index, it could be that you’re using an unsupported operator.

Filtered indexes in SQL Server only support simple comparison operators. If you use an unsupported operator or expression, you’ll likely get the dreaded “Incorrect syntax” error.

To fix, either use a supported operator, or create a view.

Example of Error

Here’s an example of code that produces the error:

CREATE UNIQUE INDEX UX_Employees_Email 
ON Employees(Email) 
WHERE EmployeeID NOT IN ( 100, 300 );

Output:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'NOT'.

It results in the 102 error. This error doesn’t tell us much, because SQL Server stopped as soon as it saw the “incorrect syntax”.

But knowing that SQL Server only support simple comparison operators, we can deduce that this is the reason for the error.

Actually as it turns out, we can use the IN operator, but not the NOT IN operator.

Solution

Microsoft suggests that one way to fix this issue is to create a view.

Another way to deal with the issue is to change our WHERE clause to use operators that are supported. For example, I could change my example to the following:

CREATE UNIQUE INDEX UX_Employees_Email 
ON Employees(Email) 
WHERE EmployeeID <> 100 AND EmployeeId <> 300;

This query produces the same result, it’s just that it’s slightly more verbose. The query you use will obviously depend on your data, and this may or may not be a suitable solution.