| 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.or
2.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 MVP
http://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 MVP
http://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 issues
But creation of clustered index will be an intensive operation one time which will take good amount of time
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 issues
But creation of clustered index will be an intensive operation one time which will take good amount of time
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://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.
Thanks
mohammad.javeed.ahmed@gmail.com |
|
|
|
|
Indexing on heaps