I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would be better:
BEGIN TRAN DELETE FROM dbo.x WHERE ID IN ( 1 ,2 ,3 ,4 ... ,100000 ) COMMIT
BEGIN TRAN DELETE FROM dbo.x WHERE ID = 1 GO DELETE FROM dbo.x WHERE ID = 2 GO ... COMMIT
DELETE FROM dbo.x WHERE ID = 1 GO DELETE FROM dbo.x WHERE ID = 2 GO ...
My assumption is that #1 would cause a scan based on sample %, pick out the items, remove them, and log it as a series of transactions. Perhaps it would base the info in the transaction log as to what pages were changed instead of each indvl. transaction, thus you can only rollback the entire activity and remark the pages, or you could commit. Is that correct?
On #2, I'm thinking does the GO statement cause a lot more transaction log activity by not allowing the SQL Server Storage Engine to roll all of these into 1 large transaction, but still provide some optimization for the transaction log with the BEGIN TRAN - COMMIT blocks, thus making it more effective than #3 but less effective than #1.
I then would assume #3 would be the worst one as each indvl. transaction is logged.
If anyone has any good blog posts or test scenarios they could point me to that would be great too. I've looked at ways to dig deep in the transaction log to figure it out myself but at this point, I thought I would ask you guys.