October 8, 2008 at 6:45 am
I have a collection of 5 columns on which I have created a UNIQUE constraint. I should have created a primary key on these 5 columns but to address some other issues, I created an Identity Column which acts as Primary key. For resolving some relationship issues, I have opted for having a single column PK.
I think that by creating a UNIQUE constraint, an index (non-clustered) is also created.
However, for performance issues, I want to have a clustered index instead.
SO, Is it just dropping one non clustered index and creating a clustered index? OR Are there some other points which I am missing?
Suggestions welcome.
Thanks
October 8, 2008 at 7:04 am
It could be an expensive operation, but it should work. Note that by default when you add a primary key constraint to a table, and a clustered index does not exist, SQL Server will create a clustered index to enforce the PK. If you rebuild the table with the five columns being part of a clustering index, and not the PK column, you will also get automatically a unique index to enforce the PK uniqueness. But in other words, it should work.
Andras
October 8, 2008 at 7:45 am
Keep in mind that if the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table.
Thus the 5 columns of the clustered index will be 'copied' to each non-clustered index, enlarging the storage needed for the indexes and generating higher I/O.
October 8, 2008 at 8:17 am
Also keep in mind that if your composite clustered index is not monotonically increasing then when you have changes you will more quickly introduce fragmentation of the index/table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2008 at 12:09 pm
Thanks you ALL for your inputs
Earlier, I also thought about issues related to Clustered Index like
- having a wide clustered index would affect performance
- it would also heavily affect non-clustered indexes as well
Creating clustered Index on my Primary key (Identity column) doesn't works well in this situation as this column isn't being used in linking with other tables nor its the main column which defines uniqueness among records.
Btw, just curious to know what should be approach in deciding upon Clustered index when uniqueness of records depends upon multiple columns. Or In other words, when one have a composite primary key.
Thanks
October 8, 2008 at 12:52 pm
The issues that everyone has raised here for correct placement of clustered indexes are all valid. It is a best practice to have a clustered index on all tables, but you have to weigh all of the aformentioned factors as to where to place the clustered index. Obviously, clustering the table on the right columns can have a postive impact on performance and table joins, but you must always consider the negative impact of page splits and wide indexes as well. As a rule, I will usually put the clustered index on some sort of incrementing column like an identity or CreateDate type column until I have identified a column or set of columns that would make more sense to order the table by.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply