April 24, 2012 at 4:07 am
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
September 18, 2012 at 9:26 pm
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.
September 19, 2012 at 2:51 am
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.
September 19, 2012 at 8:14 am
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.
September 19, 2012 at 1:15 pm
September 19, 2012 at 9:11 pm
...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.
September 20, 2012 at 9:39 am
TRUNCATE can actually be rolled back. See my recent thread here:
http://www.sqlservercentral.com/Forums/Topic1361513-1292-1.aspx
October 2, 2012 at 2:11 am
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'.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply