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

[kkffjjman]

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.

 

[gmmastros]

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

 

[kkffjjman]

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)?

 

[gmmastros]

http://www.mssqltips.com/tip.asp?tip=1080

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

 

[kkffjjman]

Great, thanks for the help! I've got what i need now. a star for the spot on help...

 

[ESquared]

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:

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.

 

[gwrman]

Good eye ESquared. in the end, the truncate command did the trick/.