Solved

TRUNCATE Table : Maintenance

Posted on 2010-09-17
11
204 Views
Last Modified: 2013-12-01
Hi,
  After truncating table what maintenance job should be done? I might have to truncate tables several times.

Thanks.
0
Comment
Question by:arunbhatt
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
ID: 33701846
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33701930
you need to rebuild index.
Why not you drop table and recreate it, instead of truncating it.
0
 

Author Comment

by:arunbhatt
ID: 33702220
Hi,
   This will be temorary processing table. What is the advantage of dropping over truncate?

Thanks.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33702324
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
 

Author Comment

by:arunbhatt
ID: 33702357
Hi,
  The temporary table would be actual table without # in front of table name. What should be the approach?


Thanks.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33702413
1. Remove its all ref., like foreign keys etc.. (remove related data from other tables)
2. Rebuild Indexs

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 334 total points
ID: 33702709
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
 

Author Comment

by:arunbhatt
ID: 33707631
Hi,
  Why TRUNCATE is better than DROP command?

Thanks.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 334 total points
ID: 33716892
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39687907
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now