Filtered Indexes VS. Indexed Views

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 ivw01

with schemabinding

as 

select ItemName, Itemid from dbo.tblItem

where 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 index

select ItemName, Itemid from tblItem with (index=ix01)

where Datecreated > convert(datetime, '2007-08-22', 101)

 

--using a filtered index

select ItemName, Itemid from tblItem with (index=ix02)

where Datecreated > convert(datetime, '2007-08-22', 101)

 

--using an indexed view

select * 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.

email it! |   |   |   |  | 

Permalink     1 Comments  

Rate this Post: 12345
COMMENTS:

Comments from the following blog entry: http://www.jabbertags.com/popular/schema

posted @ Wednesday, March 11, 2009 3:10 AM

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]