Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to T-SQL DML Level 11: How to Delete Rows from a Table Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2012 4:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:50 AM
Points: 1,040, Visits: 274
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1288765
Posted Tuesday, September 18, 2012 9:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:48 PM
Points: 9, Visits: 100
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.
Post #1361118
Posted Wednesday, September 19, 2012 2:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:47 AM
Points: 8, Visits: 63
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.
Post #1361197
Posted Wednesday, September 19, 2012 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:00 AM
Points: 1, Visits: 63
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.
Post #1361387
Posted Wednesday, September 19, 2012 1:15 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 27, Visits: 454
http://dbace.us/repriser/sqlpass/DeleteLargeWithBatch.htm

Jason
http://dbace.us
Post #1361585
Posted Wednesday, September 19, 2012 9:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 1:28 PM
Points: 101, Visits: 132
...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.



Post #1361708
Posted Thursday, September 20, 2012 9:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:01 PM
Points: 643, Visits: 3,695
TRUNCATE can actually be rolled back. See my recent thread here:

http://www.sqlservercentral.com/Forums/Topic1361513-1292-1.aspx
Post #1362091
Posted Tuesday, October 02, 2012 2:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 824, 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'.
Post #1366892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse