Filtered Indexes
Prior to SQL Server 2008 we can define index only on entire table, with filtered index we can define index on a filtered data or subset of data in the table. The definition of a filtered index contains where clause that determines if a row in the table will be index or not. The B-Tree containing the rows from the filtered index will contain only those rows which satisfy the filter criteria used while creating the index. These types of indexes are well-suited for scenarios where you frequently query a specific subset of data, for example date ranges etc.,
A normal clustered or non-clustered index would be created on entire table and size of index would be larger and occupy more space.
The filtered index statistics are more accurate because they consider only specific set of rows and due to smaller size it reduces cost/overhead of updating the statistics.
The impact of DML is less in the filtered index as it is updated only if an insert or update will satisfy the filter.
A filtered index can be rebuilt online. You can create only non-clustered filtered index, it means you cannot created clustered filter index.
Examples
Create Nonclustered index FI_EOJ on Employees(DOJ) where DOJ is not null
Create NonClustered Index FI_DeptID on Employess(DeptID) where IsActive = 1
Select Example by forcing Index (No need to provide index name)
Select * from Employee with (index(FI_DEPTID)) where isactive = 1
Note – If you pass variable or constant to where condition the optimizer will not choose filter index.
Prior to SQL Server 2008 we can define index only on entire table, with filtered index we can define index on a filtered data or subset of data in the table. The definition of a filtered index contains where clause that determines if a row in the table will be index or not. The B-Tree containing the rows from the filtered index will contain only those rows which satisfy the filter criteria used while creating the index. These types of indexes are well-suited for scenarios where you frequently query a specific subset of data, for example date ranges etc.,
A normal clustered or non-clustered index would be created on entire table and size of index would be larger and occupy more space.
The filtered index statistics are more accurate because they consider only specific set of rows and due to smaller size it reduces cost/overhead of updating the statistics.
The impact of DML is less in the filtered index as it is updated only if an insert or update will satisfy the filter.
A filtered index can be rebuilt online. You can create only non-clustered filtered index, it means you cannot created clustered filter index.
Examples
Create Nonclustered index FI_EOJ on Employees(DOJ) where DOJ is not null
Create NonClustered Index FI_DeptID on Employess(DeptID) where IsActive = 1
Select Example by forcing Index (No need to provide index name)
Select * from Employee with (index(FI_DEPTID)) where isactive = 1
Note – If you pass variable or constant to where condition the optimizer will not choose filter index.
No comments: