SQL Server, like any industrial-strength database engine, provides built-in support that enables you to wrap one or more queries inside a transaction. Local transactions (those that deal with only one physical database) operate in one of four transaction modes:
The autocommit transaction mode is the default transaction mode. Under this mode, SQL Server ensures data sanctity across the lifetime of the query execution, regardless of whether you requested a transaction. For example, if you execute a SELECT query, the data will not change over the execution lifetime of the query. Likewise, if you execute a data manipulation language (DML) query ...