Oracle client by default starts an implicit transaction for all the connections. But in SQL Server by default implicit transactions are OFF which results in automatic committing of all queries which we execute in SQL Server Management Studio/Workbench. But there is a way by which we configure SQL Server 2005 Management Studio/Workbench to start implicit transactions just like oracle client.
Let us see how to enable implicit transactions in SQL Server 2005 Management Studio/Workbench
- Open SQL Server 2005 Management Studio/Workbench
- Choose the menu item Tools-->Options..; opens Options window
- In the left side of the window, navigate the tree view to Query Execution/SQL Server/ANSI; displays a set of options on right side panel.
- Select SET IMPLICIT_TRANSACTIONS to true by ticking the check box
- That's all from now onwards all the new connections opened through Management Studio/Workbench starts an implicit transactions