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

  • Greg Larsen

    SSC-Insane

    Points: 20605

    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

    SSC Enthusiast

    Points: 101

    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

    SSC Journeyman

    Points: 90

    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

    SSC Rookie

    Points: 33

    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

    Hall of Fame

    Points: 3503

  • joevi

    SSC Veteran

    Points: 210

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

    SSCoach

    Points: 15486

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

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

  • marlon.seton

    SSCrazy

    Points: 2563

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply