How to prevent SSMS from commit automatically

 By default, SSMS perform auto commit after each DML and/or DDL statement. So the question is, what shall we do to prevent SSMS perform auto commit and control when to commit or rollback?

 

                Here is the answer. As shown below, From SSMS open Toolsàoptions

Then from left tree open Query ExecutionàSQL SERVERàANSI

Then from right select “SET IMPLICIT_TRANSACTIONS” THEN CLICK ok. Close your Query Analyzer session. Open new one and enjoy controlling when to commit or rollback.

 

 

 

 

Be careful and don’t worry

Be careful when to use commit or rollback. If you use commit, all your session transactions will be committed from opening your session or from last time you used commit. And if you use rollback, all your session transactions will be rolled back from opening your session or from last time you used rollback.

Don’t worry if you forgot using commit all rollback. If so, and you gone to close SSMS or your session, SSMS will appear the below message to ask you if you want to commit all your session transactions or rollback all of them.