delete without commit

  • I would like to test a delete statement, but I don't want to commit it yet.

    I know I can use a select statment to do exactly the records that delete will do, so that I can confirm.

    Can I also use:

    Begin tran;

    then run the delete statement, then I can see how many rows are deleted, and I also can do a select from the table to see if those rows are deleted,

    Above is a test.

    SO then I want to rollback the delete.

    I use Rollback,

    is this the right way to test a delete?

    And when everything is confirmed, then I ran commit.

  • That's exactly what I do.

    The only other option I can think of involves restoring to a point in time prior to your delete - definitely more cumbersome than using BEGIN TRANS ; DELETE;ROLLBACK and the restore option also requires more privileges.

  • You can, but remember the old irish saying "There is many a slip between the cup and the lip"

    Set up a test database or sandbox as I call it

    Test your T-SQL

    If it works as you require it to work

    Backup the production database

    then run the T-SQL in the production database.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • One thing you can also do is something like:

    CREATE TABLE #Test

    (

    ID INT IDENTITY PRIMARY KEY,

    ID2 INT

    )

    INSERT INTO #Test (ID2)

    SELECT 1

    UNION ALL

    SELECT 2

    UNION ALL

    SELECT 3

    UNION ALL

    SELECT 4

    UNION ALL

    SELECT 5

    UNION ALL

    SELECT 6

    UNION ALL

    SELECT 7

    UNION ALL

    SELECT 8

    DECLARE @TempDeletedTable TABLE

    (

    ID INT,

    ID2 INT

    )

    BEGIN TRANSACTION

    BEGIN TRY

    DELETE FROM #Test OUTPUT DELETED.* INTO @TempDeletedTable WHERE ID2 > 4

    END TRY

    BEGIN CATCH

    ROLLBACK

    END CATCH

    ROLLBACK

    SELECT * FROM #Test

    SELECT * FROM @TempDeletedTable

    The cool thing about using a table variable is that a ROLLBACK does not remove the records from it. So, you can keep what was deleted. You can even then insert it into a real table outside of the TRANSACTION block, so you can store the records for later analysis.

    One very important thing though - if you use the BEGIN TRANSACTION - ROLLBACK approach, MAKE SURE YOU ALSO PUT A TRY-CATCH BLOCK IN! If for whatever reason an error occurs in the statements being executed inside the transaction block, the ROLLBACK statement will never be reached, which means the transaction will not roll back!

  • kramaswamy (7/12/2011)


    If for whatever reason an error occurs in the statements being executed inside the transaction block, the ROLLBACK statement will never be reached, which means the transaction will not roll back!

    In that case the transaction will just remain open and you can run rollback manually. SQL doesn't automatically commit if there was a begin transaction and no commit or rollback.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Oh? cool, didn't know that. But does the transaction remain open only for the current session in question? IE, if you were using SSMS, and you began a transaction which then stopped because of an error, then you closed the window you were looking at, would the transaction automatically rollback or commit, or what would happen? The first ROLLBACK or COMMIT it encountered would trigger it, regardless of the context?

  • transactions are connection based so if the connection is closed before the commit then everything is rolled back.

    The probability of survival is inversely proportional to the angle of arrival.

  • Here is a neat form of the DELETE query shown above that I find useful at times:

    WITH cte

    AS (

    SELECT *

    FROM #Test

    WHERE ID2 > 4

    )

    DELETE FROM cte

    OUTPUT DELETED.*

    INTO @TempDeletedTable ;

    It looks a little odd at first but it allows us to highlight just the SELECT portion in the CTE and run it to see which rows will be affected by the delete without making any code changes...and it has the exact same plan as the standard DELETE syntax.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks all, great tips.

Viewing 9 posts - 1 through 9 (of 9 total)

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