quote:
Great article. One disadvantage to a clustered index that I hear a lot of people claim is disk space. How much disk space does a clustered index actually take up compared to the table it is indexing?
Thanks for the compliments and for commenting. First of all, I'd say that if I had to choose between extra disk space or the problems described in the article, I'd choose disk space any time. Now, to answer your question, I'd say that extra disk space for a clustered index is normally not going to be that much of an issue.
Remember, the leaves are the actual data, even if you drop the clustered index these pages will still be (more or less) the same. So it it only the levels of the index tree above the leaves that are interesting. How much space they use of course depends on the data in your table, how much data you have and how wide your index key is. Since you need one index row for every data page (i.e. the leaves of the clustered index), this means that to determine the number of index pages at the level above the leaf level you can use this formula:
number of pages devided by (8096 bytes per index page divided by the index key row size)
This gives us the number of index pages at the level above the leaf level. You can then take this number of index pages and use that as the first part of the formula (number of pages) to determine the number of index pages needed at the next level. When you end up with just a single page you've found the root level.
In Inside SQL Server 2000 Kalen Delaney has an example of a table with 10.000 data pages (remember each page contains 8KB of data), with a clustered index on a fixed-length character column of 5 bytes. With overhead this makes each index key row 12 bytes.
10000 / (8096 / 12) = 15
This means that at the level above the leaves 15 index pages are necessary. The index rows for these 15 pages can then be contained on a single page at the level above, so that is the root page. So, the extra size for this table with a clustered index on the char(5) column is 16/10000, i.e. less than 1%. This number (1% extra size for a clustered index) is normally a good estimate, normally even a bit high as shown in the example. As always you must remember to keep index key size as small as possible of course.
PS. Note that fillfactor and padindex configurations are not taken into account here. DS.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)