(New to SQL Server) I am struggling to understand purpose of Nonclustered indexes in SQL Server. Read though MS documentation (both for v2012 and v2008 that has pretty pictures) and some online resources, but still have a lot of unanswered questions.

  1. Does noncl. index maintains sorted B-tree ? As far as I understood, it does - http://technet.microsoft.com/en-us/library/aa933130%28v=sql.80%29.aspx, otherwise analogy of book index would not make any sense.
  2. Why on earth you want noncl. index to be defined on table or view with a clustered index or on a heap ? Heap has no order, so defining noncl. index on heap makes some sense - you can quickly find exact matches. However, table with clustered index is already organized data, and defining noncl. index will only speed up exact searches.

You've got two questions in here:

1. Does the nonclustered index have a sorted B-tree? Yes, because you have to get to the right page of the index to find the data you're looking for.

2. Why do you want a nonclustered index when you also have a clustered index? Think of the white pages of the phone book as your clustered index. If you wanted to find all of the people in your city with a first name of "Brent", then the clustered index isn't going to do you much good - the clustered index is based on last name, first name, middle initial. Wouldn't it be helpful to have a separate phone book sorted by first name?

Just like in real life, the answer to that might be yes or no - but it depends on how often you search for people by first name, and how often people move in/out of your city, or how often people change names.

  • Brent, thank you for answer. I have been enjoying Brent & Co. videos on Youtube. MS docs, your videos, along with several blogs are the best source of information I found so far. It seems like, tables that don't get updated too often, and queried for exact value and for ranges of values might a good candidates to have both types of indexes. Now it all makes sense !
    – newprint
    Mar 25 '14 at 23:34
  • Cool, glad I could help.
    – Brent Ozar
    Mar 26 '14 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.