Solved

delete v/s delete top()

Posted on 2013-06-18
1
248 Views
Last Modified: 2013-06-18
hi guys

If i have 20000 rows to delete fronm table2

I have this sql

DELETE Top (5000) table2
where NOT EXISTS
( select 1 from table1 where table1.ID = table2.ID)

Does this sql delete 5000 rows in batches ? so there will be 4 batches?
The advantage of doing this is it wont fill up the transaction log..

Is my understanding correct.


what i am trying to understand

case1:
DELETE Top (5000) table2
where NOT EXISTS
( select 1 from table1 where table1.ID = table2.ID)

v/s

case2:
DELETE T2 from table2 T2
where NOT EXISTS
( select 1 from table1 T1 where T1.ID = T2.ID)

when do i use case1 v/s case2 ?

Thanks
0
Comment
Question by:royjayd
1 Comment
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 39256816
With your 2 case scenarios, usually I do something similar to case 1 when:
1) I have many rows to delete
2) The table I'm deleting from is used by other people/processes

If I have to do 20,000 deletes from a table that is being used a lot, my large delete process will lock them all out and prevent them from doing anything.  You can even do a WHILE loop with WAITFOR to automate the whole delete process:

WHILE ((SELECT COUNT(1) FROM your_table) > 0)
BEGIN
	DELETE TOP (100) FROM your_table;   -- delete some rows
	WAITFOR DELAY '0:00:59';   -- sleep for 59 seconds
END

Open in new window



Use case 2 when it doesn't matter (ie you have the database to yourself or if you do lock the table for an extended period of time you know it wont effect anybody else)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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