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