Key Points of SQL server try catch and transaction

Msdn provides a good explanation of transaction and error handling.

If you have any questions, you can check that.

Here we will extract some important words.

"
If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction.

 

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error.

 

It is important to keep transactions as short as possible. when a transaction is started, a database management system (DBMS) must hold your resources until the end of the transaction to protect the atomicity, consistency, isolation, and durability (ACID) properties of the transaction.

 

A try... CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

 

Errors trapped by a CATCH block are not returned to the calling application ."

(If you want to return an error to the program or client, use RAISERROR)