Developing Microsoft SQL Server Databases — Question 17

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?

Answer options

• A. FILLFACTOR = 0
• B. STATISTICS_NORECOMPUTE = OFF
• C. STATISTICS_NORECOMPUTE = ON
• D. FILLFACTOR = 80

Correct answer: D

Explanation

Setting FILLFACTOR to 80 allows for some empty space on each page, which can accommodate future inserts and updates without causing page splits. In contrast, a FILLFACTOR of 0 would fill pages completely, increasing the chances of page splits, while the STATISTICS_NORECOMPUTE options do not directly address the issue of page splits.