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 INDEX idx_test_data_nonfiltered
    ON test(data)




We will use filtered index here to filter null values

CREATE UNIQUE INDEX idx_test_data_filtered
    ON test(data) 

where data is not null



No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts