Question

Point out the wrong statement.

(a) If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure

(b) When you create a clustered index on a table that has nonclustered indexes, you must have available as free space

(c) When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in master database

(d) All of the mentioned

I got this question by my college professor while I was bunking the class.

Question is taken from Indexing Strategies topic in section Performance Tuning and Optimization of SQL Server

Answer 1

Right choice is (a) If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure

The best I can explain: tempdb is used to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure.