February 5, 2012 at 3:44 pm
I have a database one of the tables has the primary key set to be nonclustered by mistake and a lot of other tables are referencing this primary key as their foreign key.
It is highly fragmented when I tried to rebuild it it is still 71% fragmented
If I changed its type from non clustered to clustered will this affect the data in the other tables
Is changing it possible .
Thanks
February 5, 2012 at 5:58 pm
Yes, it will have a major effect. I strongly suggest you do some basic reading on indexes.
Start with this series:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
February 6, 2012 at 5:13 am
Changing it from non-clustered to clustered won't affect the data in other tables, no.
There are a ton of other affects you're going to see, yeah, but no, it shouldn't change the data in other tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2012 at 10:49 am
You reference in this post, and another, the 71% fragmentation. However, you have not provided the size in pages of the index. Fragmentation on small indexes is unavoidable and does not affect performance in many cases. Please provide that information on your post. In terms of clustering the index or not, read Gail's suggested articles and ask yourself if there is a reason for it to be clustered? Not all primary keys should be clustered, so you will need to look at your situation and answer that for yourself.
Jared
CE - Microsoft
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply