Lets discuss about the rollback code or syntax in the SQL-Server. In SQL Server Settings, we generally set auto-commit on. I have worked on many projects where database is SQL-Server and most of them(team mates) were not aware of Rollback syntax in SQL-Server. In oracle, auto-commit is generally set to off. So, we always press on commit button in SQL Developer or any other tool or we use syntax “Commit;” to commit the uncommitted transactions like insert/update.
In SQL Server, If you want to explicitly want to commit the transactions , use the below code.
set implicit_transactions on
update TABLE_A set COL_A=’ABCD’ where COL_B=2
commit
In between these two lines of code, you can mention single or multiple DML statements depending on yours project requirements. In case, DML command fails due to any reason (constraints error, Integrity error etc.), It will rollback all records which are even updated/Inserted successfully as commit is explicitly mentioned in the code.