Having discussed about indexes into some level, now it is time to discuss new index type which came along with SQL Server 2008, filtered index.

In filtered index, you can define index to the filter portion of your data. For example, you can create a non-clustered as following.

CREATE NONCLUSTERED INDEX NCI_Employee

ON HumanResources.Employee(EmployeeID)

WHERE Title= 'Marketing Manager'

NCI_Employee index will create index on EmployeeID where Title equal to Marketing Manager.

Let us analyze this using Execution plans as we did before.

SELECT he.EmployeeID,he.LoginID,he.Title

FROM HumanResources.Employee he

WHERE he.Title = 'Marketing Manager'

CREATE NONCLUSTERED INDEX NCI_Employee

ON HumanResources.Employee(EmployeeID)

WHERE Title= 'Marketing Manager'

SELECT he.EmployeeID,he.LoginID,he.Title

FROM HumanResources.Employee he

WHERE he.Title = 'Marketing Manager'

First query will run without a filtered index, while second query will have the filtered index.

So you can see the difference where first query has 24 % query cost and with the filtered index , query cost has gone down to 20%.

Now let us analyze this with another scenario as showed below.

SELECT he.EmployeeID,he.LoginID,he.Title

FROM HumanResources.Employee he

WHERE he.Title = 'Marketing Manager'

SELECT he.EmployeeID,he.LoginID,he.Title

FROM HumanResources.Employee he

WHERE he.Title = 'Quality Assurance Supervisor'

Again you can see first query will use the non-clustered filtered index key, while second query does not have an index to use. Hence it will use clustered index key.

Limitations.

1. Filtered index can be created only on non-clustered indexes.

2. Index has to be simple query. You cannot use something like this.

CREATE NONCLUSTERED INDEX NCI_Employee2

ON HumanResources.Employee(EmployeeID)

WHERE ContactID IN (SELECT ContactID FROM Person.Contact WHERE EmailPromotion =2 )

3. Index where clause cannot have LIKE clause.

4. Index cannot be created on Text,ntext,varcahr(max),nvarchar(max), spatial data type columns.