Transactions in SQL Server are fundamental to ensuring data integrity and consistency. A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Here are the key concepts and examples:

Properties of Transactions (ACID)

Transactions adhere to the ACID properties:

1. Atomicity: Ensures that all operations within the transaction are completed successfully. If any operation fails, the entire transaction is rolled back.

2. Consistency: Ensures that the database transitions from one valid state to another valid state.

3. Isolation: Ensures that operations within a transaction are isolated from other transactions until the transaction is complete.

4. Durability: Ensures that once a transaction is committed, the changes are permanent, even in the event of a system failure.

Types of Transactions

1. Implicit Transactions: Automatically start a new transaction after the previous transaction is committed or rolled back.

2. Explicit Transactions: Manually start and control the transaction using BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.

3. Autocommit Transactions: By default, each individual statement is treated as a transaction and is automatically committed.

Example of an Explicit Transaction

BEGIN TRANSACTION;

BEGIN TRY
    -- Insert operation
    INSERT INTO Employees (EmployeeID, FirstName, LastName)
    VALUES (1, 'John', 'Doe');
    
    -- Update operation
    UPDATE Employees
    SET LastName = 'Smith'
    WHERE EmployeeID = 1;

    -- Commit the transaction if all operations succeed
    COMMIT;
END TRY
BEGIN CATCH
    -- Rollback the transaction if any operation fails
    ROLLBACK;

    -- Handle the error
    DECLARE @ErrorMessage NVARCHAR(4000);
    SELECT @ErrorMessage = ERROR_MESSAGE();
    PRINT 'Error occurred: ' + @ErrorMessage;
END CATCH;

Savepoints

Savepoints allow you to roll back part of a transaction without affecting the entire transaction.

BEGIN TRANSACTION;

-- First operation
INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (2, 'Jane', 'Doe');

-- Savepoint
SAVE TRANSACTION SavePoint1;

-- Second operation
INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (3, 'Alice', 'Johnson');

-- Rollback to savepoint if needed
ROLLBACK TRANSACTION SavePoint1;

-- Commit the transaction
COMMIT;

Isolation Levels

Different isolation levels control the visibility of changes made by one transaction to other concurrent transactions:

1. Read Uncommitted: No locks are placed. Allows dirty reads.

2. Read Committed: Default isolation level. Prevents dirty reads.

3. Repeatable Read: Prevents dirty and non-repeatable reads.

4. Serializable: Highest isolation level. Prevents dirty, non-repeatable reads, and phantom reads.

5. Snapshot: Provides a snapshot of data as it was at the start of the transaction, preventing dirty reads.

You can set the isolation level using the SET TRANSACTION ISOLATION LEVEL statement:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
-- Your transactional code here
COMMIT;

Transactions are vital for ensuring reliable and consistent database operations. If you'd like to explore more about transactions or any specific aspect, feel free to let me know!