CREATE TABLE TestTable (a int, b varbinary(4));
IF EXISTS ( SELECT name from sys.indexes
WHERE name = N'TestTabIndex'
AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;GO
The above CREATE INDEX statement generates error.
To rectify it, we have to use CONVERT or CAST
CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);GO
Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.
Another Issue is that the query optimizer will not use a filtered index if the query can return rows that are not in the filtered index. If a filtered index is used explicitly as a table hint and the filtered index might not contain all of the query results, the query optimizer generates query compilation error 8622.
SET QUOTEDIDENTIFIER ON/OFF can also generate the error. This thing is explained here