(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.
- 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.
- 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.
Best Answer
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.