Effect of Truncate table on a Clustered Index

A data-warehouse table with about 18M rows has a clustered index on it. This table is truncated and updated every day (We build day-over-day comparisions)

Since all the data has gone, do I need to REBUILD the Clustered Index before I start filling the table up with data?

In other words, is there any unwanted residue left after the table is truncated that I need to get rid of?
more ▼

asked Dec 17, 2011 at 03:49 AM in Default

Raj More gravatar image

Raj More
1.7k 80 82 84

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

2 answers: sort voted first
Rebuilding the clustered index before doesn't make sense: the table is empty. It's just that truncating is resetting the high-water mark instead of deleting records. But the result is the same.
more ▼

answered Dec 17, 2011 at 07:38 AM

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 20 25 31

I'm not sure I understand the "high-water mark"
Dec 17, 2011 at 08:05 AM Raj More
Assume your table is a bottle. Every time a record is added, some water is added. If you write a sign on the bottle indicating how much water is in the bottle, this is called a "high-water mark". SQL also keeps track of how much records are in a table (also called high-water mark). If you truncate a table, you're just resetting the high-water mark to zero without actually deleting the records (emptying the bottle). That's the reason why truncating a table is a very fast operation. See http://msdn.microsoft.com/en-us/library/ms177570.aspx to learn more about truncating a table
Dec 17, 2011 at 08:26 AM Wilfred van Dijk
(comments are locked)
10|1200 characters needed characters left

I agree that it wouldn't make sense to rebuild the clustered index after the truncate. The table is empty at that point, and so is the index.

Something you do have to consider though, is the fact that the clustered index will have to be updated for each record (or batch of records) you isert into the table. That is a pretty expensive exercise, and you could possibly improve the performance of the process significantly by either disabling or removing the clustered index before loading...and then rebuilding it after all the data has been loaded.
more ▼

answered Dec 21, 2011 at 07:12 AM

Martin Schoombee gravatar image

Martin Schoombee
221 2

@Martin Would it not actually require MORE processing and IO when building the Clustered Index AFTER inserting into the table?
Dec 21, 2011 at 07:32 AM Raj More

It depends on how you are inserting the records into this table. If it all happens in one transaction (i.e. all records are inserted in one batch) then it probably would be the same. But if it happens in multiple batches/transactions, the clustered index would require multiple updates and that would definitely be more expensive.

Also have a look at the execution plan. With the clustered index in place, you may see a "sort" operation in the execution plan which is also very costly.

In my experience, it is always much faster to insert data into a heap, and build/enable the indexes afterwards. But test it in your environment to make sure. In my opinion it will.
Dec 21, 2011 at 07:46 AM Martin Schoombee

Batches of major updates/insert operations are certainly faster with the non-clustered indexes disabled during the processes.

I have not tested it, but it seems counterintuitive that this would apply to clustered indexes though. It also might matter whether or not the inserts where coming in so that the clustering key values were ever increasing during the inserts.
Dec 21, 2011 at 09:43 AM TimothyAWiseman

I have to respectfully disagree with the comment that is doesn't apply to clustered indexes.

I have found when inserting a large amount of data into a table with even just an identity field as clustered index, the process took much longer than inserting into a heap.

Granted, it does depend on quite a few things...but a clustered index is still an index, and updating it multiple times will take longer than building it once-off.
Dec 21, 2011 at 09:56 AM Martin Schoombee
(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



Answers and Comments

SQL Server Central

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



asked: Dec 17, 2011 at 03:49 AM

Seen: 4470 times

Last Updated: Dec 17, 2011 at 03:49 AM