• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

delete v/s delete top()

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
royjayd
Asked:
royjayd
1 Solution
 
nemws1Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now