SQL Server Interview Question | What is the difference Between Index Rebuild and Index Reorganize

In this video you will be learning how to answer SQL Server DBA interview Question "What is the difference Between Index Rebuild and Index Reorganize?"

Complete list of SQL Server DBA Interview Questions by Tech Brothers

[rahulshah]

Thank you. It’s been 10 years since I had to maintain my own databases. I wanted to make sure I remembered things correctly.

[jeffmoden]

I appreciate your efforts. Anyone that steps up to the plate to share information is great in my book.

There's a technical error in your description of Index Rebuild. Don't feel bad though... the exact same error is in the Microsoft documentation. The error is that you and the documentation say that the index is dropped and rebuilt. That's actually totally backwards. A brand new copy of the index is first created. Once it has been created and committed, only then is the original index dropped. That's also why a huge amount of unwanted free-space is left in the file after rebuilding huge tables. Both copies of the index exist temporarily and then the original is dropped.

As for the 5/30 recommendation... that's actually not and was never meant to be a supposed "Best Practice". For more on that and the claims (again, also in the Microsoft documentation) that Reorganize is "less resource intensive than Rebuild", you might want to watch the following 'tube. It destroys all those "myths" and mis-documentations.

Again, none of this is because you've done anything wrong. We all wanted what they implied to be true but, sadly, it's not only NOT a "Best Practice", it's actually a "Worst Practice", IMHO.

Here's the link to the 'tube I'm talking about. It's not just about GUIDs... I use GUIDs a lot in the explanations because they're the proverbial "Poster Child" of fragmentation. If we can fix those, there's a whole lot more that can be fixed. And be sure to stick around after the Q'n'A where I show the results of a real shocker.


And, yeah... there are some "errata" notes where I misspoke in a couple of places... it was a real early-in-the-morning presentation and I couldn't get my mind in gear 100%.

[SanjayKattimani-tech]

Great explanation of rebuild and reorg indexes. Thanks.

[LIVdaBrand]

Thanks! we definitely used this for the NWS/NOAA!  great job!

[korlakuntanagendrababu]

Nice information, it is helpful concept, Thank you!

[IshwarRajak]

for my table fragmentation is 98 percent so I tried to rebuild but after rebuild still fragmentation is 98 percent. what needs to so in such a situation?