SQL SERVER – Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

11 SQL SERVER   Non Clustered Index and Automatic Rebuild   Quiz   Puzzle   11 of 31

A performance tuning expert often encounter situation where it is difficult to figure out why certain activities are happening behind the scene in SQL Server Engine. Understanding that every action has some root cause is the key to be successful performance tuning expert. Here is one question which often puzzles man: When are the non-Clustered Indexes automatically rebuilt?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Though the concepts of automatic rebuilding are central to the SQL Server Engine, the differences of Rebuilding and reorganizing is also worth knowing. This Hint has an explanation to the reasons when to use what. Knowing these will help a long way if you are a budding DBA.

Notes of Pinal Dave

I believe that there exists more misconception related to Indexes than any other topic in SQL Server. Reorganizing and Rebuilding are very important concepts. Additionally, the fill factor matters the most. This puzzle is so interesting that it is best-proved if T-SQL code is written to verify this puzzle. If the code is interesting, I promise to post it on this blog with due credits to you.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#67
Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script
Fragmentation – Detect Fragmentation and Eliminate Fragmentation
Generate Report for Index Physical Statistics – SSMS
Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005


There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

4 thoughts on “SQL SERVER – Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31

  1. Hi Pinal,
    I have one more doubt.
    When i rebuild or reorganize Clustered index,the averge fragmentation value is reduced as 0.But While rebuild or reogranize non clustered index ,the averge fragmentation values not redudec as less than 10 or 0.it having same value only.Kindly give advice for rebuild or reorganziese index for non clustered index



  2. Pingback: SQL Interview Quiz Part II | Vinod Kumar (Blog home)

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s