Explain filtered indexes in sql server 2008?
Submitted by: AdministratorFiltered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.
The benefits of Filtered indexes are:
Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.
Ex: CREATE NONCLUSTERED INDEX FI_Employee_DOJ ON Employee(DOJ)
WHERE DOJ IS NOT NULL
In the above example the NOT NULL is the filtered criteria for the index. Employee is the table and DOJ is the column name.
Submitted by: Administrator
The benefits of Filtered indexes are:
Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.
Ex: CREATE NONCLUSTERED INDEX FI_Employee_DOJ ON Employee(DOJ)
WHERE DOJ IS NOT NULL
In the above example the NOT NULL is the filtered criteria for the index. Employee is the table and DOJ is the column name.
Submitted by: Administrator
Read Online SQL server 2008 Job Interview Questions And Answers
Top SQL server 2008 Questions
☺ | Explain Sparse Columns of sql server 2008? |
☺ | Do you know PowerShell included in SQL Server 2008? |
☺ | Explain how to store and query Spatial Data? |
☺ | What is Change Data Capture (CDC) feature in sql server 2008? |
☺ | Do you know Enhanced database mirroring in sql server 2008? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |