May 20, 2010 at 2:57 am
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
May 20, 2010 at 3:12 am
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
May 24, 2010 at 7:21 am
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;-)
May 26, 2010 at 6:51 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply