Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Indexing on heaps

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -