Filtered Index in SQL Server 2008/Denali

Filtered Index is nothing but just a feature of Nonclustered index which I shown in previous two articles. It is just a nonclustered index with WHERE clause in simple terms.
It is mainly used while you have big tables and you used toselect only subset of data from that table. Like you have one big customertable and have one field of “Reference Person” in that table, it has NULL valueif customer directly comes to us and has reference person’s name, if customercame from any of the reference. In this case if you want only those customerslist that has reference person so that we can distribute some sort ofconsolation to those reference people.
The main advantage of “Filtered Index” is, it will havelower amount of rootpages to store the data as it will consider only those rows which cater theneeds of “Where” clause of “Filtered Index”.
Less number of pages means reduced storage size.  Since “Filtered Index” has only those data inroot pages which caters the need of “Where” clause, means when you perform anyDML operation like Insert, Delete or Update, “Filtered Index” will get effectonly if it affects the Index Key which comes under the “Where” clause of Indexso low maintenance cost. 
BTW, you can’t create “Filtered Index” on View but it willsurely get benefit of the “Filtered Index” created on base table.
Let us check the impact of  “Filtered Index” practically.
–create onedatabase which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orderstable 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 fackrows 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 thefollowing query with execution plan together and see the results in executionplan
–you can seeexecution plan with the following steps
–first selectboth of the below given query
–Press Ctrl+M
–press F5
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
CREATE NONCLUSTERED INDEXidx_SQLHubFilteredIndex1 ONSQLHubFilteredIndex1(OrderDate)
WHERE OrderDate is not null
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
GO
–if you wish,you can uncomment below code and delete SQLHub database
—-use master
—-go
—-dropdatabase sqlhub
You can see in above screen shot that the same query ranfaster 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 adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: