Search This Blog

Tuesday, November 12, 2013

Filtered Indexes

If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.
For example;

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;

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);

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

No comments:

Post a Comment