| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2013-04-01 : 02:41:37
|
| Dear Experts,Need your suggeastions.I have few tabes on my database which have no indexes on them.I am planning to create clustered index on each table.There are no unique columns on tables.so my question is:1.Is it better to create composite clustered index by making unique combination of columns.or2.shall i create a new indentity column and then create a clustered index on it?Thanks,Javeed.mohammad.javeed.ahmed@gmail.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 02:58:10
|
| I would prefer 2 as it will have much less overhead due to its size in bytes,Uniqueness and sequential nature------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-04-01 : 03:09:05
|
quote:
Originally posted by visakh16
I would prefer 2 as it will have much less overhead due to its size in bytes,Uniqueness and sequential nature------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thanks Visakh.one more question,just clearing my doubt.will there be any issue if i create a new identity column and create a clustered index on a huge table say with 10 million rows?mohammad.javeed.ahmed@gmail.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 05:47:49
|
| Nope...Not issuesBut creation of clustered index will be an intensive operation one time which will take good amount of time------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-04-01 : 05:51:01
|
quote:
Originally posted by visakh16
Nope...Not issuesBut creation of clustered index will be an intensive operation one time which will take good amount of time------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thanks a lot Visakh.mohammad.javeed.ahmed@gmail.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 07:30:02
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-04-01 : 17:51:12
|
| Devil's Advocate: Not to get all $ellCo on you but there is a case to be made for using natural vs surrogate keys. If you had to make a unique key out of a combination of columns, what size would the resulting key be? If it's about the size of your surrogate key, which I assume is an int, the performance difference could be negligible. Also, have you considered a clustered key that is not unique? SQL will add a uniquifier (is that a word?) to your clustered column(s) if necessary. Finally, since you have a set of columns that are unique, were you planning on enforcing that at the DDL level with a constraint or index anyways?=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-04-02 : 02:02:59
|
quote:
Originally posted by Bustaz Kool
Devil's Advocate: Not to get all $ellCo on you but there is a case to be made for using natural vs surrogate keys. If you had to make a unique key out of a combination of columns, what size would the resulting key be? If it's about the size of your surrogate key, which I assume is an int, the performance difference could be negligible. Also, have you considered a clustered key that is not unique? SQL will add a uniquifier (is that a word?) to your clustered column(s) if necessary. Finally, since you have a set of columns that are unique, were you planning on enforcing that at the DDL level with a constraint or index anyways?=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Thank you for the valuabe points.The combination of columns would be big size as most of the columns are character datatypes.So, as per visakh's suggestion I planned to go with adding a new identity column and then add a clustered index on it.Thanksmohammad.javeed.ahmed@gmail.com |
|
|
|
|
Indexing on heaps