March 2, 2006 at 9:25 am
Hello Everyone.
I have a question for the experts on Indexes.
My Database designed such a way that most of the tables have GUID as primary key or part of Primarykey . Unfortunately I don't have an alternate key. (The application model is such that application runs on different mobile systems(laptop, customer sites etc). and end of day we sync all the data into centralized database to analyse). That is why I used GUID as primarykey.
Some of the tables there will be very minimal inserts in a day. And some of them will have tons of inserts, updates , deletes in a day.
Is it advisible to leave the primarykey as Non clustered index or do I have to have atleast one clustered index on a table.
Can you guys help me to finetune the database to avoid any future performance issues with your advise.
1. What will the drawbacks of having no Clustered index on a table? (for both static & transactional table)
2 will having just only nonclustered cause a performance bottleneck in future when I plan to partition the table.
Please help.
Thank you all.
March 2, 2006 at 9:59 am
>>1. What will the drawbacks of having no Clustered index on a table? (for both static & transactional table)
Table fragmentation. Maintenance utilities like DBCC DBREINDEX() will not be able to reduce fragmentation in data pages if there is no clustered index
>>2 will having just only nonclustered cause a performance bottleneck in future when I plan to partition the table.
There will be a performance bottleneck regardless - the "heap" of data pages will become fragmented, requiring more IO for all operations.
You should try to find candidate columns for a clustered index on all your tables.
March 3, 2006 at 2:34 pm
Thanks a bunch..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply