Delete records where "something" without logging

[skyline666]

Hi,

I need to be able to delete a lot of records from a table but not log the transactions (to save time). I know theres a Truncate table command, but I need to be able to delete records with a where condition aswell. Does anyone know how to do this?

Many thanks in advance,

Andrew

 

[TysonLPrice]

I can't answer your question but as I was looking into it to try I was surprised to find that truncate does do minimal logging. I always thought it didn't do any. It is at the page level from what I read. This is the example that was posted to prove it.

CREATE TABLE ##Temp(Id int Identity(1,1),Name Varchar(100))

INSERT INTO ##Temp(Name) VALUES('Testing')
INSERT INTO ##Temp(Name) VALUES('Testing')
INSERT INTO ##Temp(Name) VALUES('Testing')
INSERT INTO ##Temp(Name) VALUES('Testing')
INSERT INTO ##Temp(Name) VALUES('Testing')


BEGIN TRAN
	TRUNCATE TABLE ##Temp
	SELECT * FROM ##Temp
	ROLLBACK
	SELECT * FROM ##Temp

drop table ##Temp

 

[gmmastros]

If you plan on deleting most of the records from a large table, you may get better performance by copying some records to a temp table, truncating the table, and then copying the data back to the main table. This, of course, will need to be wrapped in a transaction, and will probably ONLY help if you have millions of records in a table and want to preserve a couple thousand.

You should also read this: faq183-3141
By deleting in batches, you may be able to speed up the total time it takes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[skyline666]

Thanks TysonLPrice for that. I'll take a look at that FAQ aswell gmmastros and probably delete in batches.