SQLServerPedia

Note: This is a simple example and YMMV. I may test queries with lookups, hashes, aggregations etc later. Let me know if you have an interesting test case.

I decided to run a quick test comparing covering filtered indexes vs. covering indexed views vs. a normal covering index. Read performance-wise, it is a statistical draw.

The DDL and queries

USE [demodb]GO  CREATE TABLE [dbo].[tblItem](    [Itemid] [int] IDENTITY(1,1) NOT NULL,    [ItemName] [varchar](100) NULL,    [Datecreated] [datetime] NULL,    [Closed] [bit] NULL,PRIMARY KEY CLUSTERED (    [Itemid] ASC) ON [PRIMARY]) ON [PRIMARY] GO create index ix01 on tblitem(Datecreated, ItemName)  create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22' go create view ivw01with schemabindingas select ItemName, Itemid from dbo.tblItemwhere Datecreated > convert(datetime, '2007-08-22', 101) create unique clustered index ix01 on ivw01(itemid)create index ix02 on ivw01(ItemName)  set statistics io on --Using a normal covering indexselect ItemName, Itemid from tblItem with (index=ix01)where Datecreated > convert(datetime, '2007-08-22', 101) --using a filtered indexselect ItemName, Itemid from tblItem with (index=ix02)where Datecreated > convert(datetime, '2007-08-22', 101) --using an indexed viewselect * from ivw01

The graphical query plans

There are no surprises in the query plan. It does a scan on the filtered index but that doesn't matter since it is ...err... filtered.

Statistics IO Output

Normal covering index:

(346129 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Filtered Index:

(346129 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Indexed view:

(346129 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Other Considerations

Some other things to consider is the fact that the filtered index is a fraction of the size of the normal index. The indexed views are an enterprise feature but filtered indexes are not. The optimizer may have a hard time choosing a filtered index but these problems are not present with indexed views. So the right choice may vary with different apps even if the schema is the same.