delete v/s delete top()

Posted on 2013-06-18
Medium Priority
Last Modified: 2013-06-18
hi guys

If i have 20000 rows to delete fronm table2

I have this sql

DELETE Top (5000) table2
( 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

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


DELETE T2 from table2 T2
( select 1 from table1 T1 where T1.ID = T2.ID)

when do i use case1 v/s case2 ?

Question by:royjayd
1 Comment
LVL 23

Accepted Solution

nemws1 earned 2000 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)
	DELETE TOP (100) FROM your_table;   -- delete some rows
	WAITFOR DELAY '0:00:59';   -- sleep for 59 seconds

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)

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

624 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