Single sql column defined as 2 separate indexes-unique and non unique?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 23, 2012 at 3:40 am

  #1529133

  Thanks Gail. If anyone else is reading this and is struggling with it as much as I was, here's a full discussion:

  http://www.sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx

  John

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  August 23, 2012 at 6:01 am

  #1529198

  John Mitchell-245523 (8/23/2012)

  ---

  opc.three (8/22/2012)

  ---

  Is this to say that in a not unique index that SQL Server will attempt to insert new index entries with an existing index key in the order of the clustered index?

  I think the entries would be inserted in the (logical) order of the non-clustered key, with the clustering key acting as a tie-breaker.

  This lead-in plus the article by Kim Tripp you posted was just what the doctor ordered, thanks. As suspected it appears that the additional consideration for sorting by the clustering key in a non-unique index'es (learned it is stored in non leaf-level pages for navigation) only bolsters the idea that we should pick an ever-increasing clustering key.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato