Unable to create a Filtered Index on a Computed Column

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.

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.

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