Filtered Index in SQL Server 2008/Denali

Filtered Index is nothing but just a feature of Non clustered index which I shown in previous two articles. It is just a non clustered index with WHERE clause in simple terms.

It is mainly used while you have big tables and you used to select only subset of data from that table. Like you have one big customer table and have one field of “Reference Person” in that table, it has NULL value if customer directly comes to us and has reference person’s name, if customer came from any of the reference. In this case if you want only those customers list that has reference person so that we can distribute some sort of consolation to those reference people.

The main advantage of “Filtered Index” is, it will have lower amount of root pages to store the data as it will consider only those rows which cater the needs of “Where” clause of “Filtered Index”.

Less number of pages means reduced storage size.  Since “Filtered Index” has only those data in root pages which caters the need of “Where” clause, means when you perform any DML operation like Insert, Delete or Update, “Filtered Index” will get effect only if it affects the Index Key which comes under the “Where” clause of Index so low maintenance cost. 

BTW, you can’t create “Filtered Index” on View but it will surely get benefit of the “Filtered Index” created on base table.

Let us check the impact of  “Filtered Index” practically.

--create one database which you can delete after running this example

create database SQLHub

GO

USE SQLHub

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('SQLHubFilteredIndex1', 'U') IS NOT NULL BEGIN

      DROP TABLE SQLHubFilteredIndex1

END

GO

--creating table

CREATE TABLE SQLHubFilteredIndex1 (ID INT IDENTITY Primary Key Clustered, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting fack rows into table

INSERT INTO SQLHubFilteredIndex1 (OrderDate, Amount, Refno)

SELECT TOP 100

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

Union All

SELECT TOP 100000

      NULL,

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

--run the following query with execution plan together and see the results in execution plan

--you can see execution plan with the following steps

--first select both of the below given query

--Press Ctrl+M

--press F5

SELECT * from SQLHubFilteredIndex1 where OrderDate is not null

CREATE NONCLUSTERED INDEX idx_SQLHubFilteredIndex1 ON SQLHubFilteredIndex1(OrderDate)

WHERE OrderDate is not null

SELECT * from SQLHubFilteredIndex1 where OrderDate is not null

GO

--if you wish, you can uncomment below code and delete SQLHub database

----use master

----go

----drop database sqlhub

You can see in above screen shot that the same query ran faster after creating index.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”