Post reply

Index key or Included columns

  • March 21, 2011 at 5:57 pm

    #234506

    Could someone please explain to me what is the main difference between the Index key column and the Included Nonkey columns.

    Would it be beneficial to add the same column in both the index key and nonkey columns when creating a new index?

    Thanks

  • March 21, 2011 at 6:26 pm

    #1301647

    Index key column is the column used in the index, and used by the optimizer to decide of the index is applicable. It's also used by the execution engine to fulfill the WHERE part of the query. This is equivalent to the stuff in the back of a book. It is used for the SELECT part of the query if the column is needed.

    An included column is added to the index, not used by the optimizer or execution engine in finding the rows or joining tables. It can only be used by the SELECT part of the query to return the data.

    There is no point in having a key column included separately. It's already in the index as a key column and if it were INCLUDEd, you would be storing it twice. I'm not positive that SQL would do this and I would hope the engine would be smart enough to not duplicate it.

  • March 21, 2011 at 6:35 pm

    #1301649

    Thanks Steve,

    That really clears things up for me now.

  • March 21, 2011 at 8:44 pm

    #1301670

    Steve, you'll be happy to know that SQL is smart enough not to allow an included column to be used if it's already in the index key:

    CREATE NONCLUSTERED INDEX ncixtest on PingResult (PingID, LastRun) INCLUDE (LastRun, IsRunning)

    Msg 1909, Level 16, State 2, Line 1

    Cannot use duplicate column names in index. Column name 'LastRun' listed more than once.

  • March 21, 2011 at 10:01 pm

    #1301675

    Cool

  • March 21, 2011 at 11:28 pm

    #1301680

    This article may be worth a read (part 3 of 3)

    http://www.sqlservercentral.com/articles/Indexing/68636/

    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

  • March 22, 2011 at 4:30 am

    #1301771

    Thanks for the links:-)

Viewing 7 posts - 1 through 6 (of 6 total)

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