Filtered indexes in SQL Server 2008

One thing which I always wanted to see is, can I create an index with selective list on a column? Lets say I have one table called Employee and 80% of the cases I query this table based on Dept_Name = ‘HR’.

SELECT Emp_id,Name FROM EMPLOYEE WHERE DEPT_Name = ‘HR’.

By looking at the query, DEPT_Name is right column to create an index. In addition to this I always retrieve the Emp_ID, Name columns from Employee. So I would like to include these columns as leaf level nodes in my index definition using Covering Index feature in SQL Server.

--Create demo table
CREATE TABLE Employee(
Emp_ID INT IDENTITY(1,1),
Name VARCHAR(30),
Date_Of_Join DATETIME,
Dept_Name VARCHAR(10))

-- Lets insert some dummy data
DECLARE @i INT = 100000
DECLARE @Dept VARCHAR(30)
WHILE @i > 0
BEGIN
IF @i <= 500
BEGIN SET @Dept = NULL
END
IF @i <= 500
BEGIN
SET @Dept = 'HR'
END
INSERT INTO Employee(Name,Date_Of_Join,Dept_Name)
VALUES ('TIM' + CAST(@i as varchar),GETDATE(),@Dept)
SET @i = @i - 1
END


Let’s create NonClusttered Covering Index
CREATE NONCLUSTERED INDEX iNdx_Employee ON dbo.Employee(Dept_Name)
INCLUDE (Emp_id,Name)

Lets the run the below query see the execution plan
SELECT Emp_ID,Name FROM Employee WHERE Dept_Name = 'HR'

If you look at the execution plan it is going for Index Scan and
Estimated SubTree Cost is 0.0045542, Estimated CPU Cost is 0.000707 and Estimated I/O Cost is 0.0038472. Which is very good.
But still I am not satisfied with the performance!! So is there any way to improve the query performance with out changing the table structure or the query??

This is where we can utilize the “Filter Index” feature in SQL Server 2008 where you can create the Index based on the list of values. This is very simple just you need to put WHERE clause to the Index creation.

CREATE NONCLUSTERED INDEX iNdx_Employee ON dbo.Employee(Dept_Name)
INCLUDE (Emp_id,Name)
WHERE Dept_Name = 'HR'

Lets the run the below query see the execution plan
SELECT Emp_ID,Name FROM Employee WHERE Dept_Name = 'HR'

If you look at the execution plan it is going for Index Seek and
Estimated SubTree Cost is 0.003832, Estimated CPU Cost is 0.000707 and Estimated I/O Cost is 0.003125. Which is extremely good.


Guidelines to create Filtered Index:
1) When you are querying the smaller sub-set of data very frequently

Advantages:
1) Improved Query performance
2) Less overhead on Index Maintenance

Restrictions on Filtered Index:
1) Must be on Non Clustered Columns
2) Can’t be created on Computed Columns.