NonClustered Index Structure

  • Comments posted to this topic are about the item NonClustered Index Structure

  • Really enjoyed this and found it very easy to follow along with.

    Well done! 🙂

  • Thanks for the good article.

  • Mayank ... this was helpful. You simplified the way SQL Server performs routine index operations. I hope all application vendors read what you wrote.

    I have had experience with mainframe and Unix databases (in addition to SQL Server). The difficulty is, if a vendor learned about indexing on Oracle or DB2, it doesn't work the same as on SQL Server. That is the problem I have for a major purchased SQL Server app we use that was originally developed on Oracle. The vendor created all the tables without the benefit of a clustering index. Yes, they created NCIs on the tables, but I told them this is only helpful if NCI is a covering index. Otherwise, it will still have to read through the heap -- row by row. Because Oracle and DB2 use RID pointers from the NCI index page to go directly to the data page, you can get away without having a clustering index. But it will definitely be problematic in SQL Server.

    BTW, I am still waiting for the vendor to add clustering indexes. I would do it myself, but my shop has a Best Practice to not modify the vendor's code for fear that they won't provide support. Meanwhile, we have sucky performance. 🙁

    Vendor developers/DBAs: are you listening???

  • Thanks all for your comments and feedback 🙂

  • Excellent Article! Very well explained and interesting to read.


  • Curious to see how it works with a non unique clustered index.

  • Excellent one...thanks mayank

  • Hi - unfortunately there's an error in each of your graphical representations of the index structure. For all non-leaf level pages on the left-hand edge of the b-tree, the first key value stored is NULL, which represents negative infinity in an ascending-key index. The key value stored is the lowest possible key value that can be on the page below, not necessarily the actual lowest value present on the page. You can see this in your screenshot of the DBCC PAGE output.


    Paul Randal
    CEO, Check out SQLskills online training! Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • This was removed by the editor as SPAM

  • Thank you for the explanation.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply