TRUNCATE Table : Maintenance

Hi,
  After truncating table what maintenance job should be done? I might have to truncate tables several times.

Thanks.
arunbhattAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
TRUNCATE is the best approach if available.

All indexes will be automatically emptied by the TRUNCATE.

I thought statistics were too, but if you want to be sure, just issue this command after the TRUNCATE:

UPDATE STATISTICS tablename

So, in summary:

TRUNCATE TABLE tablename
UPDATE STATISTICS tablename
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should rebuild the indexes, to ensure statistics are updated (otherwise sql might "think" the table is still "full" in regards to evaluating a explain plan ...)
0
 
Mohit VijayCommented:
you need to rebuild index.
Why not you drop table and recreate it, instead of truncating it.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
arunbhattAuthor Commented:
Hi,
   This will be temorary processing table. What is the advantage of dropping over truncate?

Thanks.
0
 
Mohit VijayCommented:
temorary processing table means? #temp table?

if yes, then you dont need to rebuide indexes etc.., only consider to shrink tempdb time by time.
0
 
arunbhattAuthor Commented:
Hi,
  The temporary table would be actual table without # in front of table name. What should be the approach?


Thanks.
0
 
Mohit VijayCommented:
1. Remove its all ref., like foreign keys etc.. (remove related data from other tables)
2. Rebuild Indexs

0
 
arunbhattAuthor Commented:
Hi,
  Why TRUNCATE is better than DROP command?

Thanks.
0
 
Scott PletcherSenior DBACommented:
TRUNCATE will be faster, since the data structure does not have to be removed.

DROP will lose all objects associated with the table, including but not limited to:
indexes
permissions
foreign keys
defaults
check constraints
etc.

ALL of that is deleted if you DROP the table.

Whereas, if you TRUNCATE the table, only the rows are deleted.
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.