March 21, 2013 at 4:26 am
hi,
i've seen a couple of cases where huge staging tables containing c/nc indexes are truncated and the index drop and rebuild happens , so its like 1. drop index 2. truncate table 3.re-create index
my question is
1. would a NC/C index slow down the truncation(not delete) of data from a huge table.
2. And most importantly, is there any advantage of re-building index on a truncated table.
i think while truncating a table , the indexes need not be touched at all.
Please enlighten me.
March 21, 2013 at 5:27 am
rohit.anshu (3/21/2013)
1. would a NC/C index slow down the truncation(not delete) of data from a huge table.
No. Truncation command dosent touch the indexes.Thats the another reason why truncate is faster.
rohit.anshu (3/21/2013)
2. And most importantly, is there any advantage of re-building index on a truncated table.
IF there is no data why re-build is required infact what it will do ? nothing.
rohit.anshu (3/21/2013)
i think while truncating a table , the indexes need not be touched at all.
Yes.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 22, 2013 at 11:56 pm
rohit.anshu (3/21/2013)
hi,i've seen a couple of cases where huge staging tables containing c/nc indexes are truncated and the index drop and rebuild happens , so its like 1. drop index 2. truncate table 3.re-create index
In this scenario dropping the table and recreating the table and indexes would likely be faster.
my question is
1. would a NC/C index slow down the truncation(not delete) of data from a huge table.
Clustered only vs. heap, likely no difference. Every extra non-clustered on the table though, yes. Index allocations must be updated during truncate operations so more index pages means more work for the truncate to do.
2. And most importantly, is there any advantage of re-building index on a truncated table.
No. No benefit. Truncated tables have no pages nor do the indexes.
i think while truncating a table , the indexes need not be touched at all.
Please enlighten me.
Non-clustered indexes have to kept in sync with the data pages, so yes, indexes are involved. The data in them is not touched directly but the index pages must be deallocated, same as the data pages.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply