If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘OR’” when creating a filtered index in SQL Server, it’s probably because you’re using the OR operator in your WHERE clause.

Filtered indexes don’t support the OR operator.

However, filtered indexes do support the IN operator, and so it’s possible to change your query to use that.

So to fix this issue, try the IN operator instead.

Example of Error

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

CREATE INDEX idx_customers_downunder ON Sales.Customers (CustomerId)
WHERE Country = 'AUS' OR Country = 'NZL';

Output:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.

This error tells us that we used an incorrect syntax in our statement, and the OR operator looks to be the likely culprit.

Solution

As mentioned, we can change the statement to use the IN operator:

CREATE INDEX idx_customers_downunder ON Sales.Customers (CustomerId)
WHERE Country IN ( 'AUS', 'NZL' );

Result:

Commands completed successfully.

This time it ran without error.

We can use the following query to take a look at it:

SELECT 
    name,
    type_desc,
    filter_definition
FROM sys.indexes
WHERE name = 'idx_customers_downunder';

Result:

name                     type_desc     filter_definition            
-----------------------  ------------  -----------------------------
idx_customers_downunder  NONCLUSTERED  ([Country] IN ('AUS', 'NZL'))

Yes, the index was created successfully.