Developing Microsoft SQL Server Databases — Question 44

You are building a new index for an application.
You need to reduce the storage space used by the index and to minimize logical reads.
What should you configure?

Answer options

• A. SORT_IN_TEMPDB = ON
• B. MAXDOP = 4
• C. PAD_INDEX = OFF
• D. DATA_COMPRESSION = PAGE
• E. DATA_COMPRESSION = ROW

Correct answer: D

Explanation

The correct answer is D, DATA_COMPRESSION = PAGE, as it effectively reduces the storage size of the index and minimizes logical reads by compressing data at the page level. Options A, B, and C do not address storage optimization or logical reads, while E, DATA_COMPRESSION = ROW, compresses data at the row level but is typically less efficient than page compression for reducing overall index size.