vsamantha35 (5/2/2016)
I wanted to know what is the difference between below 2 sets (Set1 & Set2).
The first requires one network round trip, the second multiple. That's all.
Second question, is that I am not using SORT_IN_TEMPDB option while rebuild, so, does the huge index rebuild guarentees that the sorts is done only in memory and doesn't spill to tempdb?
Not at all. Without that option, the sort space is allocated in the user database which contains the indexes. With it, the sort space is allocated in TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability