I was trying to improve the performance of some code recently and decided that a filtered index could really help me out. Upon attempting to add the index I got an error stating that you cannot add filtered indexes to computed columns. This is the case even if the computed column is persisted.
That’s a shame as this would be a really useful thing to have.
There is actually an open Connect item on this. It’s been open for 4 years now, so I’m not sure that there will ever be any traction on it, but feel free to upvote it yourself.
Code to repeat the problem below:
CREATE TABLE #SomeData
RowID INT NOT NULL CONSTRAINT PKSomeData PRIMARY KEY
, Quantity INT NOT NULL
, Price MONEY NOT NULL
, TotalCost AS (Quantity * Price) PERSISTED
CREATE INDEX IDX_CostGreaterThan10 ON #SomeData (RowID) WHERE TotalCost > 10;
Msg 10609, Level 16, State 1, Line 1
Filtered index ‘IDX_CostGreaterThan10’ cannot be created on table ‘#SomeData’ because the column ‘TotalCost’ in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.