Filtered Index is a new feature in SQL Server 2008, it is an optimized non-clustered index created on a subset of data. The definition of the index will have where clause in it. It provides huge performance improvement when we query a subset of data from a large table. These filtered indexes are relatively small when comparing to normal Indexes and queries will be less expensive in terms of I/O.
Note: We can’t create a filter index on complex WHERE clause queries and it doesn’t allow LIKE in where clause, we can use simple operators. Filtered Indexes can be rebuild online.
CREATE INDEX idx_hostName ON Total_Hosts(ServerName) WHERE Active = 1
SELECT si.index_id, si.name, si.type_desc, si.filter_definition FROM sys.indexes si, sys.tables st
WHERE si.object_id = st.object_id AND st.name= ‘Total_Hosts’
No comments: