Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

drop, and rebuild table, or delete all rows? 1

Status
Not open for further replies.

kkffjjman

Technical User
Feb 7, 2008
11
CA
I have an application that tracks a weekly SLA for something, and resets at the end of the week.

the table used is ONLY used for that, and so, we need to essentially empty the table at the end of the week. (Sunday midnight)

Question - which is better (for size and performance)

a) - just delete all rows?
b) - drop the table and recreate

its a ms SQL database.
 
There is a 3rd option, which I recommend.

[tt][blue]Truncate Table [!]TableName[/!][/blue][/tt]

If you have referential integrity set up on this table, you may not be able to truncate it. If you can, though, it will be the fastest way to remove all the rows from it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
thanks, i didn't think of that.

can you explain exactly what happen when you run a Truncate sql on a table?

Specifically, does it delete the rows AND free up all space previously used? (whereas simply deleting rows does not free up the space, apparently)?
 

Neither command will free up all previously used space. To do that, you'll need to issue another command.

Do a little research on DBCC SHRINKDATABASE and DBCC SHRINKFILE.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Great, thanks for the help! I've got what i need now. a star for the spot on help...
 
The article George linked implies that truncation can't be rolled back:

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary.
This is false. Here's the proof:

Code:
create table t (a int)
insert t select 1
select * from t
begin tran
truncate table t
select * from t
rollback tran
select * from t
drop table t
This also is misleading:

When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
The implication that TRUNCATE doesn't require DBCC SHRINKDATABASE is also false. Deallocating a database object does not recover the space it used on disk. Don't confuse physical allocation with logical allocation.

I'd also like to see some support for the "may hang onto" assertion. Some digging into how pages are allocated and assigned, and deallocated is in order. And I don't trust that article.
 
Good eye ESquared. in the end, the truncate command did the trick/.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top