Estimating the size of a non-clustered index
After all the fun and games when doing this for a clustered index, I said I\’d post up the non-clustered version if it was interesting.
Turns out, it\’s not that different from the clustered index version, so if you\’ve already gone to the trouble of doing that, then you\’re most of the way there.
Again, there\’s a very good MSDN HowTo about it at:
http://msdn.microsoft.com/en-us/library/ms190620.aspx
…so I won\’t repeat either that or my own calculations from before. One thing to bear in mind, though, is the provision for estimating any indexes with included columns (one of the nicest indexing changes in SQL2005, in my opinion), but it\’s all detailed in the HowTo.
Of course, you may not always have a clustered index on your table, but you\’ve at least got a primary key, so that counts 🙂
Oh, and just an aside about column nullability : I don\’t mean to teach you to suck eggs or anything, but please give nullability a thought. When you\’re estimating ANY index size, it\’s quite important.
If a column can contain nulls, SQL Server has to keep track of it using a null bitmap. This null bitmap has a storage overhead in the index, so when dealing with very large tables with several indexes, you might want to keep nullable columns to a minimum. I\’m just sayin\’…..
Of course, there is a whole other piece on SQL 2008 Sparse Column support, but that brings with it it\’s own idiosynchrasies (?) when it comes to indexing and data compression, so I\’ll attempt to cover those off in a later post. Maybe.
Back soon !
The Modern Dataist 