Primary key vs index with include columns

• PiMané

  SSChampion

  Points: 13965

  More actions

  November 6, 2009 at 6:06 am

  #217157

  Hi,

  I've a table with about 8 columns.

  There's a primary key on col1 and col2.

  SQL Server advises to add a new index on col1 and col2 including col5. (SELECT col5 FROM table WHERE col1 = ... and col2 > ...).

  Is this logical or not, since the pk is clustered and the data is "attached" to the clustered pk index?

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 6, 2009 at 6:17 am

  #1075863

  Hi Pedro

  It depends...

  SQL Server missing index advisor suggests way to many indexes - in my opinion. If your query is executes really often, the index might make sense. Otherwise one step from PK to data page should be okay.

  At the end of the day, the only correct answer seems to be "try both ways and compare the cost/effort".

  Greets

  Flo

• PiMané

  SSChampion

  Points: 13965

  More actions

  November 6, 2009 at 10:18 am

  #1076016

  If I have an index idx5, with 5 columns, and another idx3, with 3 columns, all included in the 5 columns' index, will the 5 columns "replace" the 3 columns when needed?

  Ex: select .. from table where c1 = ... and c2 = ... and c3 = ... (being c1, c2 and c3 the common columns)

  if the 5 columns as c4 and c5 also, will I have to use them (if c4 is int and always > 0 then I put c4 > 0, the same for c5) or if the idx3 columns' order is c1, c2, c3 and idx5 is c4, c1, c5, c2, c3 SQL will always tell to create idx3?

  is the column's order important when creating the index or will SQL know to "solve" the problem?

  I remember DB2 had a big problem on an earlier version cause if you had an index with c1, c2 and used in the where clause c2 and c1 it wouldn't know to use the index...

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• PiMané

  SSChampion

  Points: 13965

  More actions

  November 6, 2009 at 10:28 am

  #1076021

  Just another question...

  What's the difference between a PK and a UNIQUE index?

  They both can be clustered, not null on all columns and the index has the advantage of being able to have INCLUDE columns...

  Does SQL have any special "treatment" for PK that doesn't have on indexes?!

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 6, 2009 at 10:53 am

  #1076033

  Hi Pedro

  First your index question:

  The order of the columns is very important for index usage:

  If you have an index containing columns (in this order):

  c4, c1, c5, c2, c3

  and you query c1, c2, c3 the index cannot be used.

  If you have an index over c1, c2, c3, c4, c5 (in this order) it will be used. The order of columns within your WHERE or JOIN clause does not matter.

  Second PK and unique constraint:

  The primary key represents the identity of a row within a table for SQL Server. Usually this should be a small column, or columns, like INT data type. A PK requires all columns to be NOT NULL. A unique works like a PK except the NOT NULL restriction. Each table allows only one PK but several unique constraints.

  Greets

  Flo

• PiMané

  SSChampion

  Points: 13965

  More actions

  November 6, 2009 at 10:57 am

  #1076037

  Thanks,

  I'll just have to do some testings and look at the execution plans and io statistics..

  Pedro

  ---

  
  If you need to work better, try working less...