Turn away pure key zealots 
I have a clustered index that starts with an INT IDENTITY(1,1) column and therefore you can only add data to the end of the cluster. What I'm confused about is the relationship between this and the fill factor. In a normal fill factor scenario you'd be worried about inserts causing page splits but if you can only append to this cluster does this mean I should set the factor to 100% even if I'm expecting a large number of inserts? Basically I don't understand what happens when you run out of space on a page on a B-tree if it's based upon an ever increasing number.
For an ever-increasing index the fill factor of 100 should indeed not increase the page splits.
It might cause hot spots having a clustered index on the ever-increasing key but after how many inserts this will become a problem really depends on your hardware (might even be several thousands a second).
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||You are correct. 100% fill factor will not cause page splits on an index that is an identity column. But why would you create a clustered index over an identity column. Yes, I understand that loading data is very fast. Data retrieval and table joins would be costly.You should use natural keys with an identity column if a primary key is required.
|||Thanks for the answers, re-assuring that I hadn't lost the plot.
re: TheBrick
I'm not telling the whole story that's why, just concentrating on the fill factor & identity issue. That's also why I said to look-away now
FYI the key is something like <Identity> + <natural key> where the idenitity is used as the join for all sorts of relationships. I won't go into the in's and out's but don't fret it needs to be there for this specific case. Yes it breaks rules, but sometimes you have to. Also there are other (obviously nonclustered) indexes that represent the more natural relationships. All this muddies the waters when asking about 100% fill factors hence I didn't include it 
No comments:
Post a Comment