asked
Is this normal for indexes from SQL 2000 to SQL 2005?
I am upgrading a db from SQL 2000 to SQL 2005. A particular table has a non clustered index on a column with 42 million row. The reserved space for the index on SQL 2005 is 162,393K in SQL 2000 it is 120,296K. Doesn't sound like to much of a big deal until you have a thousand indexes. In doing some testing I have dropped the index in both instances and recreated and have the same result. It's a simple non clustered index ASC order on a single column. The only thing I have found different is 2005 reports 161640 pages and 2000 is reporting 119853 pages. Would having a clustered index on the table in one instance and not the other have anything do with the size difference of the non clustered index?
> Would having a clustered index on the > table in one instance and not the > other have anything do with the size > difference of the non clustered index? Yes, it would or at least it could. If the table has a clustered index then all index records on the **leaf pages** of the non-clustered index will store the respective clustered index' record value for bookmark lookups. If the table is a **heap** then all **leaf pages** of the non-clustered index will store (I believe) the 16 byte pointer uniquely identifying a row on the heap (for the same reason, bookmark lookups). For example, suppose I have a table named **quotes** where the daily quotes from the market are kept. If this table (say 1 million records) is clustered by quote\_date datetime (8 bytes), company\_id int (4 bytes) then the size of the clustered data stored on the leaf pages of some non-clustered index of this table will be 1,000,000 \* (8 + 4) = 12 MB. If I drop both indexes and then create only non-clustered one that this size will be 1,000,000 \* 16 = 16 MB. The difference is 4 MB per 1 mln records. If the table were clustered by just an artificial int key then such difference would be 12 MB per 1 mln records. It is nearly impossible to find a justification for NOT creating a clustered index on the table with millions of records. Heaps are OK to have if they are highly volatile, with frequent inserts and deletes and no updates resulting in the record count differing greatly during the day and hopefully having almost no records at the end of the day if everything runs as expected, but beyound this scenario, they are seldom justified. Oleg
@Tim 1 I don't believe that dropping a clustered index will free up any space because it will probably increase the size of the non-clustered index. Personally, I would never dare to have a 40 million rows table without clustered index. One of my answers includes some talk about indexes: and most importantly, it has a link to Gail Shaw's **Introduction to indexes** articles, which are undoubtly the best I have ever read. As far the datetime as a candidate for a clustered index is concerned, it really depends. While clustered index is not required to be unique, SQL Server will make it unique under the hood by appending so-called uniquefier (6 to 8 extra bytes not available to us to view). So, if the values are not unique then something else should be used instead. Please read Gail's article, they will clear your questions.
Great explanation, this was driving me crazy today. I couldn't find a proper way to word it in a search to get a direct answer. In the case of the database I am working with the data is static, no updates, deletes, or inserts. I created the clustered index to move the data into file groups. I guess it wouldn't hurt to drop the clustered index to free up 120 GB. This database stores historical check image data for 7 full years. It has made it for 8 years with heaps. Any advantage of having this data with a clustered index on date rather than non clustered? Everything is already separated into quarterly tables with date constraints.
@Oleg - late to the party on this one, but +1 - another awesome answer.
