This will be a great performance on larger table where a good chunk of queries work on smaller portion of the table.A regular index will be on entire table ignoring the fact that most of the queries are interested in a smaller portion of the index.This makes the index deeper and need more pages to store the index b tree structure which results in more IO. Where as filtered index will be created only for a portion of the table and hence less pages are required to store the index.
Let us consider an example of salesorder table which contain data for last five years. Major part of active queries on this table are based on the last calender year and the current calender year.A simple example of filtered index will be like this.
CREATE NONCLUSTERED INDEX ix_salesorder_Filter
ON salesorder(SalesOrderId,OrderDate,Status,Customer_id,TotalDue)
WHERE OrderDate>'2012-01-01'
CREATE TABLE Product
(
Productid INT NOT NULL PRIMARY KEY,
ProductCode CHAR(10) ,
ProductName VARCHAR(100)
)GO CREATE UNIQUE INDEX ix_Unique_Filtered ON Product(Productcode) WHERE productcode IS NOT NULLGOINSERT INTO Product VALUES(1,'AR-5381','Adjustable Race')INSERT INTO Product VALUES(2,NULL,'Bearing Ball')INSERT INTO Product VALUES(3,NULL,'BB Ball Bearing')INSERT INTO Product VALUES(4,'AR-5381','Adjustable Race-Small')
We are able to insert multiple record with NULL value in the productcode column, but when we are trying to insert a record with duplicate value 'AR-5381', it is not allowing to insert duplicate value into the Productcode column. The unique filtered index defined on this table help us to enforce this kind of uniqueness.
Read operation: The above said is one of the common usage of filtered index.Another usage of filtered index is to support the queries. Let us see a sample below.USE mydbGO SELECT * INTO SalesOrderheader FROM AdventureWorks2008.Sales.SalesOrderheaderGO--Unique Clustered indexCREATE UNIQUE CLUSTERED INDEX ix_SalesOrderheader ON SalesOrderheader(SalesOrderid)GO--Filtered IndeCREATE INDEX ix_filtered_index ON SalesOrderheader(orderdate) WHERE orderdate>'2008-01-01'
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'GOSELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate='2008-03-01'GOSELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate='2007-12-01'
An index is defined on the column orderdate for the records which are ordered after 2008-01-01. There are three select query with different condition on the orderdate. The first one is trying to fetch a range of records which comes under the filtered criteria (sub set of filtered result). Second query try to select records which ordered on specific date which comes under the filter criteria.The third one also try to fetch the orders placed on a specific date, but that date is not come under the filtered range.
The first two queries are made use of the filtered index and the third one gone for clustered index scan. This is because the third query can not be served from filtered index as the predicate of the query is not comes under the filter criteria used in the index creation statement.In short , the records needed to perform the third select operation is not available in the filtered index.The filtered index gives lots of performance improvement for this type of queries especially when the number of records filtered out are much more than the number of records satisfying the filtering criteria.
CREATE INDEX ix_TerritoryID_Filter ON SalesOrderheader (OrderDate) WHERE TerritoryID<=5
GO
SELECT salesorderid,orderdate FROM SalesOrderheader WHERE
TerritoryID<=5
GO
SELECT salesorderid,orderdate FROM SalesOrderheader WHERE
TerritoryID=4
Let us see the execution plan of the two select statement.
The predicate of the first statement is exactly matching with the where condition of the filtered index creation statement. The second statement, is actually a subset of filtered index, but as the columns territoryid is not part of the index , SQL server will not be able to filter the records and hence execution is performed by clustered index scan.
Limitation of filtered index The concept of filtered index is very attractive but there are some limitation in the usage of filtered index , especially when it comes to parameterization.Let us rewrite the query
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'as
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>@Orderdate
The execution plan is not utilizing the filtered index when we have rewritten the query with local parameter. The reason behind that is , at the compile time , the query optimizer does not know what value will be passed for the parameter @OrderDate. So optimizer has to generate a safe plan to satisfy all the criteria. This is the same issue when we change the database property to forced parameterization or defined the statement as procedure.
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'
GO
CREATE PROCEDURE GetSalesorder (@OrderDate date) ASBEGINSELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate=@OrderDate END
In both these cases optimizer will not use the filtered index as it does not know what value will be passed during the run time and it try to generate a safe plan with out considering the availability of filtered index.
While we changing the database property parameterization to forced, the optimizer will replace all the static predicate with local variable. For example , the statement
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'
will be treated as below by the optimizer
DECLARE @Orderdate date='2008-05-01'
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>@Orderdate
DECLARE @Orderdate date='2008-05-01'DECLARE @SQL NVARCHAR(1000)SET @SQL=N'SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'''+CAST(@Orderdate AS CHAR(10))+''''EXEC (@SQL)GO
The execution plan is given below
From the execution plan it is clear that ,it is making use of the filtered index.
If you liked this post, do like my page on FaceBook
