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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Larsen » Stairway to T-SQL DML Level 11: How to Delete...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Greg Larsen

Posted Tuesday, April 24, 2012 4:07 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

luke.warneminde

Posted Tuesday, September 18, 2012 9:26 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:39 PM
Points: 9, Visits: 90

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

andrew.robertson

Posted Wednesday, September 19, 2012 2:51 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:18 PM
Points: 2, Visits: 25

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

WBarrios

Posted Wednesday, September 19, 2012 8:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 10:38 AM
Points: 1, Visits: 45

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

jswong05

Posted Wednesday, September 19, 2012 1:15 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:54 AM
Points: 27, Visits: 421

http://dbace.us/repriser/sqlpass/DeleteLargeWithBatch.htm

Jason
http://dbace.us
Tongue

Post #1361585

Joe-10953

Posted Wednesday, September 19, 2012 9:11 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 07, 2013 5:23 PM
Points: 101, Visits: 102

...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

Chrissy321

Posted Thursday, September 20, 2012 9:39 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 573, Visits: 3,074

TRUNCATE can actually be rolled back. See my recent thread here:

http://www.sqlservercentral.com/Forums/Topic1361513-1292-1.aspx

Post #1362091

marlon.seton

Posted Tuesday, October 02, 2012 2:11 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 569, Visits: 201

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 »

Permissions