You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table named Claims. The structure of the Claims table is as shown in the following table.
Only two percent of the claims are open at any point in time. You discover that queries on claims that have an Open status take a long time to execute. You need to optimize the performance of the claim-processing queries. What should you do?
A.
Use a partitioning function to partition the Claims table on the open_date column.
B.
Create a view for the Claims table by using a WHERE clause to include all rows that have a NULL valuein the close_date column.
C.
Create an index for the Claims table by using a WHERE clause to include all rows that have a NULL value in the close_date column.
D.
Create a table-valued function for the Claims table by using a WHERE clause to include all rows thathave a NULL value in the close_date column.
Explanation:
When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.