Post reply

NonClustered Index Structure

  • April 10, 2016 at 4:45 am

    #309784

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

  • April 11, 2016 at 7:09 am

    #1871123

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

    Well done! 🙂

  • April 11, 2016 at 7:14 am

    #1871129

    Thanks for the good article.

  • April 11, 2016 at 9:09 am

    #1871175

    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???

  • April 11, 2016 at 9:38 am

    #1871186

    Thanks all for your comments and feedback 🙂

  • April 11, 2016 at 11:48 am

    #1871223

    Excellent Article! Very well explained and interesting to read.

    Cheers!

  • April 11, 2016 at 6:54 pm

    #1871299

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

  • April 14, 2016 at 1:51 pm

    #1872166

    Excellent one...thanks mayank

  • May 31, 2018 at 11:44 pm

    #1992595

    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.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul 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

  • June 1, 2018 at 2:55 am

    This was removed by the editor as SPAM

  • June 6, 2018 at 8:37 am

    #1993094

    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