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 * 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
—-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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

One thought on “Filtered Index in SQL Server 2008/Denali

  1. Pingback: Milestone of 500+ article

Comments are closed.