For both online and offline index REBUILDs (and assuming the index is over 128 extents (or 1024 pages), which is only 8MB), a brand new index will be created and the old one won't be dropped until the new one commits.
If the REBUILD is done with (ONLINE=ON), then a 3rd object is built behind the scenes to handle any changes that occur to the data in the index while the index is being rebuilt. Once the new index is built, the changes will be applied.
The ONLINE=ON option is quite a bit slower than ONLINE=OFF because (supposedly... I've not taken the time to confirm) it's checking for changes in the process even if no changes occur. My personal observation is that, for some reason that I've also not taken the time to look into, ONLINE=ON also doesn't do quite as good a job at rebuilding the index either.
To summarize... my personal observations have been that, if I can, I'll use WITH (ONLINE=OFF) whenever I can because its so much faster and does a better job of rebuilding than WITH (ONLINE=ON).
As a bit of a sidebar, I also NEVER use REORGANIZE except when I need to compact LOBs, which is NEVER because I do other things to avoid even that. REORGANIZE is second only to DBCC SHRINKFILE in screwing up indexes. If I don't have the time to REBUILD an index and can't do it ONLINE, I'll just leave it be until I can REBUILD it rather than screw it or my system up with REORGANIZE.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)