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

  • 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

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

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

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

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

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

  • 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