Solved

delete v/s delete top()

Posted on 2013-06-18
1
249 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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