Solved

delete v/s delete top()

Posted on 2013-06-18
1
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

705 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