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 PletcherConnect With a Mentor Senior 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]Connect With a Mentor 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 PletcherConnect With a Mentor Senior 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.