Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Question about included columns in nonclustered indexes: Where exactly are they stored? RE: Question about included columns in nonclustered indexes: Where exactly are they stored?

  • October 1, 2008 at 1:34 pm

    #879712

    GilaMonster (10/1/2008)

    Maxer (10/1/2008)

    It seems like it grabs Phone and Email from its leaf level, but still has to jump to the clustered index to get the Fname and Lname since it doesn't actually store those data values in the index, just pointers to them...

    FName and LName are the index keys, so they are stored at all levels of the index. They have to be, how else would SQL be able seek on those columns.

    In a nonclustered index, the nonclustered index keys and the clustered index keys are stored at all levels of the index. The include columns are only stored at the leaf level. The clustering keys are there as a 'pointer' to the row, in case a bookmark lookup is required.

    Ah, of course. Indeed otherwise how would it be able to seek on those columns 🙂

    Well that explains that.

    Thanks for pointing out my obvious logical error there. I knew I was failing to take something into account, and that would certainly be it!