February 20, 2005 at 4:57 pm
A question came up in conversation with a coworker and I thought I would it post on this site. If a table has a Clustered Primary Key with Identity Seed Increment 1, does that prevent the Index from becoming fragmented from inserts? The argument was that inserts would occur sequentially ordered so no index fragmentation should occur. I would also like to pose the same question but substitue table fragmentation instead of index fragmentation.
Thanks!
February 20, 2005 at 10:39 pm
Hai,
If you use an identity column as your unique clustered index you can greatly reduce the amount of fragementation because all of your inserts will occur at the end of the table. You need to experiment to find a good fill factor for you particular situatiuon to minimize page splits from updates.
But remember identity has got its own ill effects
Experiment on things
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
February 21, 2005 at 1:45 pm
The argument was that in the case above, there would be no fragementation and therefore no reason to ever reindex the table. Also, no reason to set a fillfactor. Does that hold true?
February 21, 2005 at 1:54 pm
No, not necessarily. NVarchar's and Varchar, as just one example, can frequently vary in size within a column. Therefore, updates to a column can cause page splits, etc. that will cause some fragmentation.
For instance, if you have a varchar(8000) with an empty string at ID 10 and someone updates it to a full varchar(8000) value, you are likely to experience some fragmentation.
I would say that your table and indexes would definitely experience LESS fragmentation, but it can still occur.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply