Filtered index

Filtered index

Filtered index is a feature added in SQL Server 2008 and serves a great purpose for some of the requirements
As a general we all know that index improves search performance if INDEX is created properly
But some cases where we font requires data from entire column but only some filtered values
Even for this we have to create index for data entire column.
Whenever we query this column it has to read index for all values  column.

But if we want only certain value from column based on some logic then is there any other option available?
In this case it would be better if we create index only filtered value of column so it will put less overhead on the
server in terms of maintenance as well as server will have to check less amount of data while reading from index

Lets create first normal index than we will create filtered index and check this.

We want to check for orderqty > 10 for all out requirements.

CREATE INDEX idx_SalesOrderDetail_OrderQty

    ON Sales.SalesOrderDetail(OrderQty)

CREATE INDEX idx_SalesOrderDetail_OrderQty_Filtered

    ON Sales.SalesOrderDetail(OrderQty)

WHERE OrderQty > 10

Lets see space used by this 2 indexes

We can see that space used by filtered index much lesser compare to non filtered index

EXEC Sp_msindexspace ‘Sales.SalesOrderDetail’

 

Ok , how to find index is filtered ?

SELECT *

FROM   sys.indexes

WHERE  object_id = Object_id(‘Sales.SalesOrderDetail’)

       AND has_filter = 1

For unique index

One more use of filtered index is for creating unique index by filtering not unique values

Let see an example

CREATE TABLE test

    (

         id    INT IDENTITY(1, 1),

         data  VARCHAR(100),

         value INT

    )

INSERT INTO test

VALUES      (‘a’,

             1),

            (NULL,

             1),

            (NULL,

             2),

            (‘b’,

             2),

            (‘c’,

             3)

SELECT *

FROM   test 

Now I want to create unique index on data column but it has multiple null values  

It will fail 

CREATE UNIQUE INDEXidx_test_data_nonfiltered

    ON test(data)

We will use filtered index here to filter null values

CREATE UNIQUE INDEXidx_test_data_filtered

    ON test(data) 

where data is not null