Sunday, August 12, 2012

Retrieve the filtered indexes on a db

Filtered indexes were introduced from SQL Server 2008 . Its a special type of Non Clustered index with a filter applied. This comes particularly handy in cases where we'e interested in only some subsets of data more frequently in a large table.
Recently there was a requirement to identify the filtered indexes existing in a db. I used the below query for identifying the filtered indexes in a db.


SELECT name AS IndexName,
OBJECT_NAME(object_id)  AS TableName,
STUFF((SELECT ',' + COL_NAME(object_id,column_id) 
FROM sys.index_columns 
WHERE index_id= i.index_id
AND object_id=i.object_id
AND is_included_column = 0
ORDER BY index_column_id
FOR XML PATH('')),1,1,'')      AS IndexColumnList,
STUFF((SELECT ',' + COL_NAME(object_id,column_id) 
FROM sys.index_columns 
WHERE index_id= i.index_id
AND object_id=i.object_id
AND is_included_column = 1
ORDER BY index_column_id
FOR XML PATH('')),1,1,'')      AS IncludedColumnList,
filter_definition AS FilterCondition 
FROM sys.indexes i
WHERE has_filter=1


Thought of sharing it here as it would benefit others too.