Home » Microsoft » MB6-886 » Which statement will successfully create a filtered index?
You manage a large database on an instance of SQL Server 2008. You have a table named TrxHistory that contains millions of rows and multiple columns, including a TrxDate column and a TrxStatus column. Most of the rows have a TrxStatus value of ‘D’, and most of your queries process only these rows for specified date ranges.
You decide to create a filtered index to improve query performance.
Which statement will successfully create a filtered index?
A. CREATE NONCLUSTERED INDEX FI_TrxStatusD ON TrxHistory (TrxDate)WITH (IGNORE_DUP_KEY = OFF)WHERE TrxStatus = ‘D’;
B. CREATE NONCLUSTERED INDEX FI_TrxStatusD ON TrxHistory (TrxDate, TrxStatus = ‘D’);
C. CREATE NONCLUSTERED INDEX FI_TrxStatusD ON TrxHistory (TrxDate) WHERE TrxStatus = ‘D’;
D. CREATE NONCLUSTERED INDEX FI_TrxStatusD ON TrxHistory (TrxDate) USING TrxStatus = ‘D’;
Correct Answer: C
Explanation/Reference:
A filtered index is a nonclustered index that is defined with a specific WHERE clause to optimize the index for specific queries. The index uses the WHERE clause condition to index only specific rows in the table. Using a filtered index can improve performance in many situations and reduce the space required for the index.
The given statement creates an index named FI_TrxStatusD on the TrxDate column of the TrxHistory table, but only includes rows with a TrxStatus value of D.
You should note that the WHERE clause cannot reference certain types of columns, such as a computed column, a user-defined type, or a geography, geometry, or hierarchyid data type.
The statement that uses the IGNORE_DUP_KEY option when creating the index is incorrect because the IGNORE_DUP_KEY option cannot be specified with filtered indexes.
The statements that specify ON TrxHistory (TrxDate, TrxStatus = ‘D’) and ON TrxHistory (TrxDate) USING TrxStatus = ‘D’ are incorrect because these statements will each generate a syntax error. To create a filtered index, a WHERE clause condition must be used.
