x

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?
more ▼

asked Sep 09 '10 at 02:17 PM in Default

Tim gravatar image

Tim
35.5k 32 40 138

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Sep 09 '10 at 04:37 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

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.
Sep 09 '10 at 05:00 PM Tim

@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: http://ask.sqlservercentral.com/questions/7320/which-index-is-best 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.
Sep 09 '10 at 08:31 PM Oleg
@Oleg - late to the party on this one, but +1 - another awesome answer.
Sep 16 '10 at 02:20 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x472
x122
x68

asked: Sep 09 '10 at 02:17 PM

Seen: 1259 times

Last Updated: Sep 10 '10 at 12:49 AM