Querying Data with Transact-SQL — Question 132

You have a disk-based table that contains 15 columns.
You query the table for the number of new rows created during the current day.
You need to create an index for the query. The solution must generate the smallest possible index.
Which type of index should you create?

Answer options

• A. clustered
• B. filtered nonclustered with a getdate() predicate in the WHERE statement clause
• C. hash
• D. nonclustered with compression enabled

Correct answer: B

Explanation

The correct answer is B because a filtered nonclustered index with a getdate() predicate will only include the rows that match today's date, making it the smallest possible index for this query. Option A (clustered) would require a larger index since it organizes all rows, while C (hash) is not suitable for range queries like dates, and D (nonclustered with compression) does not specifically target the date filter, potentially resulting in a larger index than necessary.