SQL Server : Rollback without BEGIN TRANSACTION

Is there a way we can rollback to previous state of the transaction using ROLLBACK without BEGIN TRANSACTION?

delete from table1;
ROLLBACK

Message:

  • How to check the SSIS package job results after it has completed its execution?
  • Select random rows and stop when a specific sum/total is reached
  • How to group rows in SQL Server in groups with constrained size without splitting
  • Horizontal date intervals to Vertical dates (possibly a sql/vba loop solution?)
  • SQL pivot not handling absence of data
  • Replace duplicate spaces with a single space in T-SQL
  • The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Any input would be of great help.

    Thanks !!!

    3 Solutions collect form web for “SQL Server : Rollback without BEGIN TRANSACTION”

    To expand on gerrytans answer when you explicitly set IMPLICIT_TRANSACTIONS ON, you can use a ROLLBACK. See the MSDN doco related to this. Note that this isn’t the default autocommit transaction mode.

    This allows me to run a statement like;

    SET IMPLICIT_TRANSACTIONS ON
    
    INSERT INTO my_table (item_type, start_date_time)
    VALUES ('TEST', CURRENT_TIMESTAMP)
    
    ROLLBACK
    
    -- Shouldn't return the 'TEST' value inserted above.
    SELECT * FROM my_table ORDER BY start_date_time DESC 
    

    As SQL server error tells you — no you can’t. And many people would be curious why would you want that in the first place.

    Keep in mind SQL server has an implicit transaction — that is for DML you issue without explicit BEGIN TRAN, SQL server will start and finish a transaction for you behind the screen.

    A common usage of ROLLBACK is for error handling. If somewhere in the middle of the transaction you realize you cannot proceed further due to bad user input or other reason — then a reasonable action is to ROLLBACK to return to the starting point

    The worst thing that can happen is leave your data state ‘somewhere in the middle’.

    You must have a BEGIN TRANSACTION before you can use the ROLLBACK command. You can’t go back to the previous state.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.