Clustered Index on column having 97% same value

• Daniel Wood

  SSC Veteran

  Points: 280

  More actions

  May 20, 2010 at 2:57 am

  #224799

  I have been looking at a RMS (Microsoft retail management system) Database for a client that is having speed issues.

  I have noticed that there are only 2 indexes on the transaction table which have been added due to a recomendation by Microsoft.

  They are

  CREATE CLUSTERED INDEX [Transaction1]

  ON [dbo].[Transaction]([RecallType])

  ON [PRIMARY]

  CREATE INDEX [Transaction2]

  ON [dbo].[Transaction]([Time], [RecallType])

  ON [PRIMARY]

  Now the question/problem I have with this is that

  Transaction Table contains 700,000 Rows

  RecallType is an int which contains 22941 different values but of these 678,500 are the value 0

  Now I cannot see what benefit this clustered index would it not be better to add the index on a more unique field.

  Cheers

  Daniel wood

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 20, 2010 at 3:12 am

  #1169955

  It probably would be better to have the cluster on a more unique column. Without analysing the system I couldn't say for 100% sure.

  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

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  May 24, 2010 at 7:21 am

  #1171540

  Daniel Wood (5/20/2010)

  ---

  I cannot see what benefit this clustered index would it not be better to add the index on a more unique field.

  here it is non uniue clus index and The engine will append a 4-byte value (when necessary) to any non-unique cluster key value as it's inserted into the index to make it unique

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• ashish 10950

  SSC-Addicted

  Points: 422

  More actions

  May 26, 2010 at 6:51 am

  #1172665

  Hi,

  it wont be used much and setting clustered index on such column is of use as per mine thought , need to know how your processes are using it . Gail is correct clustered index should be more on unique column , or you can use a unique column alone with these and set it as a primary key . But cannt say much without knowing the utilization