March 26, 2012 at 2:30 am
I have one table with around 14 columns. The issue is that only after selecting around 10 columns gives me the uniquely identified id. But definitely it wil be a bad idea to have combination of those to have a primary key/clustered index as the index will become bulkier. If I choose uniqueidentifier column, it will also not be a good idea [As i have read in few of the articles] because of the performance issue. What should i do in tshi scenario ?
March 26, 2012 at 2:49 am
My standard answer would be to add an int indentity column if you can. Narrow, unique and ever increasing is a good candidate for your clustering key. Keep in mind, the clustering key does not have to be your primary key, so you can still use the same primary key as before if that is desireable.
And finally, I strongly recommend The Clustered Index Debate by the one and only Kimberly L. Tripp.
March 26, 2012 at 4:45 am
In my case this table gets populated daily with around 1000 records and also we daily purge around 800 records from this table. Will that be a good idea to have identity column in this case ?
March 26, 2012 at 5:23 am
Also want to know if having too many group of columns in the unique constraint can have any performance issues
March 26, 2012 at 6:55 am
In my case this table gets populated daily with around 1000 records and also we daily purge around 800 records from this table. Will that be a good idea to have identity column in this case ?
Also want to know if having too many group of columns in the unique constraint can have any performance issues
March 26, 2012 at 7:06 am
sqlnaive (3/26/2012)
In my case this table gets populated daily with around 1000 records and also we daily purge around 800 records from this table. Will that be a good idea to have identity column in this case ?
Yes
Also want to know if having too many group of columns in the unique constraint can have any performance issues
Unique constraint no, clustered index yes.
Just make sure that doesn't go over the 900 bytes allowed for a index key.
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
March 26, 2012 at 7:28 am
Gail,
I have a real time scenario where this one table has 15 columns and out of which 12 are in non clustered unique index.
Now for some particular condition, to achieve uniqueness, we have to add one extra column to this non clustered unique index.
But this column is of variable character with length 1200. Adding this will be bad. Could you suggest me some solution ?
March 26, 2012 at 7:34 am
You really need 13 out of 15 columns in a table to define uniqueness? None of those 13 columns depend on any of the other 13 columns?
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
March 26, 2012 at 3:24 pm
Max key length for an index is 900 bytes, so a varchar(1200) should not be used as an index key. You could consider crating a hash of it, and use the hash in the unique index. It does strictly speaking not guarantee uniqueness, but it may be the only solution.
March 27, 2012 at 2:18 am
GilaMonster (3/26/2012)
You really need 13 out of 15 columns in a table to define uniqueness?
Yes. For few entries this is the case.
GilaMonster (3/26/2012)
None of those 13 columns depend on any of the other 13 columns?
No.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply