CREATE INDEX with INCLUDE question

• Dave-42203

  SSC Enthusiast

  Points: 195

  More actions

  October 16, 2008 at 9:42 am

  #194692

  I have been trying tofind an answer to a question for ages now, buit still not managed to find an definite answer.

  If I have two indexes, using the same key(s), but different INCLUDE columns, can I merge the two indexes into one, and add the INCLUDEd columns together.

  eg.

  Existing Indexes

  CREATE NONCLUSTERED INDEX Index1 ON Table1 (Column1) INCLUDE (Column2,Column3,Column4)

  CREATE NONCLUSTERED INDEX Index2 ON Table1 (Column1) INCLUDE (Column2,Column3,Column5)

  Replace With?

  CREATE NONCLUSTERED INDEX Index1 ON Table1 (Column1) INCLUDE (Column2,Column3,Column4,Column5)

  Thanks

  Dave

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 16, 2008 at 9:52 am

  #885941

  Dave (10/16/2008)

  ---

  I have been trying tofind an answer to a question for ages now, buit still not managed to find an definite answer.

  If I have two indexes, using the same key(s), but different INCLUDE columns, can I merge the two indexes into one, and add the INCLUDEd columns together.

  eg.

  Existing Indexes

  CREATE NONCLUSTERED INDEX Index1 ON Table1 (Column1) INCLUDE (Column2,Column3,Column4)

  CREATE NONCLUSTERED INDEX Index2 ON Table1 (Column1) INCLUDE (Column2,Column3,Column5)

  Replace With?

  CREATE NONCLUSTERED INDEX Index1 ON Table1 (Column1) INCLUDE (Column2,Column3,Column4,Column5)

  Thanks

  Dave

  I would think so. Since the include fields are just there to cover queries, combining the two would make sense most of the time.

  The only times I would not see it making sense would be if column5 or column2+column3+column4 were "big", or if one of the two got updated a lot omre than the other: that could make the combined index noticeably less efficient, and both queries these are designed to cover would suffer. If you were to keep them separately, one of them might run quite a bit faster due to more efficient storage within the index.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Dave-42203

  SSC Enthusiast

  Points: 195

  More actions

  October 16, 2008 at 9:58 am

  #885946

  Matt,

  Thank You.

  That seems to confirm my thinking too.

  Dave

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 16, 2008 at 1:02 pm

  #886066

  Yes, you can replace the two indexes. With the include columns, the order they appear doesn't matter.

  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