Unable to create a Filtered Index on a Computed Column

Question

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST
(
    ISNULL(
        CASE WHEN sintMarketID = 2 
            AND strType = 'CARD'
            AND strTier1 LIKE 'GG%' 
        THEN 1 
        ELSE 0 
        END
    , 0) 
    AS BIT
) PERSISTED;

The calculated column is PERSISTED, and according to computed_column_definition (Transact-SQL):

PERSISTED

Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.

But when I try to create an index on my column I get the following error:

CREATE INDEX FIX_tblBGiftVoucherItem_incl
ON dbo.tblBGiftVoucherItem (strItemNo) 
INCLUDE (strTier3)
WHERE isUsGift = 1;

Filtered index 'FIX_tblBGiftVoucherItem_incl' cannot be created on table 'dbo.tblBGiftVoucherItem' because the column 'isUsGift' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

How can I create a filtered index on a computed column?

or

Is there an alternative solution?

Answer 1

Unfortunately as of SQL Server 2014, there is no ability to create a Filtered Index where the Filter is on a Computed Column (regardless of whether or not it is persisted).

There has been a Connect Item open since 2009, so please go ahead and vote for it. Maybe Microsoft will fix this one day.

Aaron Bertrand has an article that covers a number of other issues with Filtered Indexes.

Answer 2

Although you cannot create a filtered index on a persisted column, there is a fairly simple workaround that you may be able to use.

As a test, I've created a simple table with an IDENTITY column, and a persisted computed column based on the identity column:

USE tempdb;

CREATE TABLE dbo.PersistedViewTest
(
    PersistedViewTest_ID INT NOT NULL
        CONSTRAINT PK_PersistedViewTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(2000) NOT NULL
    , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
);
GO

Then, I created a schema-bound view based on the table with a filter on the computed column:

CREATE VIEW dbo.PersistedViewTest_View
WITH SCHEMABINDING
AS
SELECT PersistedViewTest_ID
    , SomeData 
    , TestComputedColumn
FROM dbo.PersistedViewTest
WHERE TestComputedColumn < CONVERT(INT, 27);

Next, I created a clustered index on the schema-bound view, which has the effect of persisting the values stored in the view, including the value of the computed column:

CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
GO

Insert some test data into the table:

INSERT INTO dbo.PersistedViewTest (SomeData)
SELECT o.name + o1.name + o2.name
FROM sys.objects o
    CROSS JOIN sys.objects o1
    CROSS JOIN sys.objects o2;

Create a statistics item and an index on the view:

CREATE STATISTICS ST_PersistedViewTest_View
ON dbo.PersistedViewTest_View(TestComputedColumn)
WITH FULLSCAN;

CREATE INDEX IX_PersistedViewTest_View_TestComputedColumn
ON dbo.PersistedViewTest_View(TestComputedColumn);

Performing SELECT statements against the table with the persisted column may now automatically use the persisted view, if the query optimizer determines it makes sense to do so:

SELECT pv.PersistedViewTest_ID
    , pv.TestComputedColumn
FROM dbo.PersistedViewTest pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)

The actual execution plan for the above query shows the query optimizer chose to use the persisted view to return the results:

You may have noticed the explicit conversion in the WHERE clause above. This explicit CONVERT(INT, 26) allows the query optimizer to properly use the statistics object to estimate the number of rows that will be returned by the query. If we write the query with WHERE pv.TestComputedColumn = 26, the query optimizer may not properly estimate the number of rows since 26 is actually considered a TINY INT; this may cause SQL Server to not use the persisted view. Implicit conversions can be very painful, and it pays to consistently use the correct data types for comparisons and joins.

Of course, all the standard "gotchas" resulting from using schema binding do apply to the above scenario; this may prevent using this workaround in all scenarios. For instance, it will no longer be possible to modify the base table without first removing the schema binding from the view. In order to do that, you'll need to remove the clustered index from the view.

If you do not have SQL Server Enterprise Edition, the query optimizer will not automatically use the persisted view for queries that do not directly reference the view using the WITH (NOEXPAND) hint. To realize the benefit of using the persisted view in non-Enterprise Edition versions, you'll need to re-write the query above to something like:

SELECT pv.PersistedViewTest_ID
    , pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
WHERE pv.TestComputedColumn = CONVERT(INT, 26)

Thanks to Ian Ringrose for pointing out the Enterprise Edition limitation above, and to Paul White for the (NOEXPAND) hint.

This answer by Paul has some interesting details about the query optimizer in relation to persisted views.

Answer 3

From Create Index and its whereclause, this is not possible:

WHERE

Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.

The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

Source: MSDN

Answer 4

• You need a column that is not calculated to put the filtered index on.
• You need to compute the value to go in that column.

Before we had computed columns we used triggers to calculate the columns value whenever the row was changed or inserted.

(A trigger could also be used to insert/remove the PK of the item from a 2nd table that was then used in queries.)

Answer 5

This is an attempt at improving Hannah's work around. In her solution, she suggests using 2 indexes on the view and a statistics object.

The 1st index is clustered, which is actually required since unlike a nonclustered index on a table, an error will be generated if creation of a nonclustered index on the view is attempted without first having a clustered index.

The 2nd index is a nonclustered index, which is used as the index behind the query. In the comments section of her answer, I asked what would happen if a clustered index were used instead of a nonclustered index.

The following analysis tries to answer this question.

I'm using the exact same code, except I'm not creating a nonclustered index on the view.

I'm also not creating a statistics object. If you are following along and using SQL Server Management Studio (SSMS) to enter the code below, you should be aware that you may see some red squiggly lines - which look like errors. These are (probably) not errors, but involve an issue with intellisense.

You can either disable intellisense or just ignore the errors and run the commands. They should complete without errors.

-- Create the test table that uses a computed column.
USE tempdb;
CREATE TABLE dbo.PersistedViewTest
(
    PersistedViewTest_ID INT NOT NULL
    CONSTRAINT PK_PersistedViewTest
    PRIMARY KEY CLUSTERED
    IDENTITY(1,1)
    , SomeData VARCHAR(2000) NOT NULL
    , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
);
GO

-- Insert some test data into the table.
INSERT INTO dbo.PersistedViewTest (SomeData)
SELECT o.name + o1.name + o2.name
FROM sys.objects o
    CROSS JOIN sys.objects o1
    CROSS JOIN sys.objects o2;
GO

The following execution plan (with no view / index view) is produced after the following query is run against the table:

SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO

This gives a baseline to compare against. Notice that after the query completed, a statistics object was created (_WA_Sys_00000003_1FCDBCEB). The PK_PersistedViewTest statistics object was created when the clustered table index was created.

Next, the filtered view and clustered index on that view are created:

-- Create filtered view on the computed column.
CREATE VIEW dbo.PersistedViewTest_View
WITH SCHEMABINDING
AS
SELECT PersistedViewTest_ID, SomeData, TestComputedColumn
FROM dbo.PersistedViewTest
WHERE TestComputedColumn < CONVERT(INT, 27);
GO

-- Create unique clustered index to persist the values, including the computed column.
CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
GO

Now, lets try running the query again, but this time against the view:

SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO

The new execution plan is now:

If the new plan is to be believed, after the addition of the view and clustered index on that view, the statistics appear to indicate that the time required to execute the query has now doubled. Also, notice that no new statistics object was created to support the new index after the query was run, which is different from the query on the table.

The query plan still suggests that creation of a nonclustered index would be quite helpful in improving the performance of the query. So, does that mean that a nonclustered index has to be added to the view before the desired performance improvement can be obtained? There is one last thing to try. Modify the query to use the "WITH NOEXPAND" option:

SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO

This results in the following query plan:

This execution plan looks quite similar to the one that was produced with the nonclustered index given in Hannah's answer. But, this one is done with one less (nonclustered) index and one less statistics object.

It turns out that the NOEXPAND option has to be used with the express and standard versions of SQL Server to make proper use of an indexed view. Paul White has an excellent article that expounds on the benefits of using the NOEXPAND option. He also recommends this option be used with the enterprise edition to ensure the uniqueness guarantee provided by the view indexes is used by the optimizer.

The above analysis was done with the express edition of SQL Sever 2014. I also tried it with the developer edition of SQL Server 2016. The NOEXPAND option does not appear to be required with the development edition to achieve the performance gains, but is still recommended.

Less than 5 months ago, Microsoft made the developer editions free. The license restricts the use to development only, which means the database can't be used in a production environment. So, if you have been looking to test out memory optimized tables, encryption, R, etc. then you no longer have the no-license excuse. I successfully installed it on my computer a few days ago along side SQL Server 2014 Express with no issues.