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 12:59 pm

    #879698

    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.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass