SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to T-SQL DML Level 11: How to Delete Rows from a Table


Stairway to T-SQL DML Level 11: How to Delete Rows from a Table

Author
Message
Greg Larsen
Greg Larsen
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16155 Visits: 290
Comments posted to this topic are about the item Stairway to T-SQL DML Level 11: How to Delete Rows from a Table

Gregory A. Larsen, MVP
luke.warneminde
luke.warneminde
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 119
Small typo: Listing 10 creates a CTE called DupRecords, while the description afterward refers to it as DupColor. Also, doesn't the partition mean that the ROW_NUMBER() applies for duplicate Names and Ids? The listing code matches your earlier statement that 'a duplicate row is a row that has the same column value for each and every column' but the discussion only refers to Name, not Name and Id.
andrew.robertson
andrew.robertson
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 81
Excellent article. Thanks.

Perhaps this is an obvious point, but we always write our "DELETE" statements as "SELECT" statements first. Then we can check exactly what rows we are going to delete. Then generally it's fairly trivial to change the select to the delete. Obviously you can't always do this. Thanks again.
WBarrios
WBarrios
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 66
No mention of contention issues?

What happens when you have to delete 100K records from 100M record table in an OLTP environment? What happens when SQL server decides to escalate the locking to the table level? It hurts.

Discussing these considerations would really push the article to the next level.
jswong05
jswong05
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2883 Visits: 476
http://dbace.us/repriser/sqlpass/DeleteLargeWithBatch.htm

Jason
http://dbace.us
:-P
joevi
joevi
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 191
...and like most other DML actions:

Always wrap the commands in USE [database]; BEGIN TRAN...COMMIT/ROLLBACK TRAN.

Won't be of much use with TRUNCATE TABLE but transaction control is always important when modifying data.



Chrissy321
Chrissy321
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13652 Visits: 5717
TRUNCATE can actually be rolled back. See my recent thread here:

http://www.sqlservercentral.com/Forums/Topic1361513-1292-1.aspx
marlon.seton
marlon.seton
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2293 Visits: 319
Good article, thank you.

One small thing, and it's nothing to do with the thrust of the article, but could people stop using 'criteria' when they mean the singular. It's 'a criterion', 'many criteria'.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search