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.
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.
