Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Transactions Introduction

Learn the basics of SQL Server Transactions, why transactions are used, and how COMMIT, ROLLBACK and SAVEPOINT work.

What is a Transaction?

A Transaction is a group of one or more SQL statements that are treated as a single unit of work.

A transaction allows us to make sure that a group of database operations is completed successfully. If something goes wrong, the changes can be rolled back.

Transactions are commonly used when multiple database operations must be performed together.

Why Do We Use Transactions?

Transactions help maintain the consistency and reliability of data in a database.

  • To group multiple SQL operations together.
  • To save changes only when all required operations are successful.
  • To undo changes when an error occurs.
  • To maintain database consistency.
  • To control when changes become permanent.

Real-World Example

Consider a bank transfer from Account A to Account B.

Two operations are required:

  1. Deduct money from Account A.
  2. Add money to Account B.

Both operations should succeed together. If money is deducted from Account A but the second operation fails, the first operation should also be undone.

Transaction Concept:
Either all required operations are completed successfully, or the changes can be rolled back.

Basic Transaction Syntax

BEGIN TRANSACTION;

-- SQL statements

COMMIT;

Here, BEGIN TRANSACTION starts the transaction and COMMIT makes the changes permanent.

BEGIN TRANSACTION

BEGIN TRANSACTION starts a new transaction.

BEGIN TRANSACTION;

After the transaction starts, SQL statements executed as part of that transaction can be committed or rolled back.

COMMIT

COMMIT permanently saves the changes made during the transaction.

COMMIT;
Example:
If all operations are successful, use COMMIT to save the changes.

ROLLBACK

ROLLBACK is used to undo changes made during a transaction that have not been committed.

ROLLBACK;
Example:
If an error occurs during the transaction, we can use ROLLBACK to undo the transaction's changes.

Simple Transaction Example

Let's create a simple transaction using a Students table.

BEGIN TRANSACTION;

INSERT INTO Students
(
    StudentId,
    StudentName,
    City,
    Age
)
VALUES
(
    105,
    'Amit',
    'Pune',
    23
);

COMMIT;

The INSERT operation is performed inside the transaction. When COMMIT is executed, the change becomes permanent.

ROLLBACK Example

BEGIN TRANSACTION;

INSERT INTO Students
(
    StudentId,
    StudentName,
    City,
    Age
)
VALUES
(
    106,
    'Sneha',
    'Mumbai',
    22
);

ROLLBACK;

In this example, the INSERT operation is undone because the transaction is rolled back.

Transaction Flow

Transaction Flow:

BEGIN TRANSACTION
↓
SQL Statements
↓
Check Result
↓
COMMIT   →   Save Changes
OR
ROLLBACK   →   Undo Changes

Important Transaction Commands

Command Purpose
BEGIN TRANSACTION Starts a transaction.
COMMIT Permanently saves the transaction changes.
ROLLBACK Undoes uncommitted transaction changes.
SAVE TRANSACTION Creates a savepoint inside a transaction.

Important Points

  • A transaction groups multiple SQL operations into a single unit of work.
  • BEGIN TRANSACTION starts the transaction.
  • COMMIT saves the changes.
  • ROLLBACK undoes uncommitted changes.
  • SAVE TRANSACTION creates a savepoint inside a transaction.
  • Transactions help maintain data consistency.

Summary

A transaction is a group of SQL statements treated as one unit of work.

SQL Server provides commands such as BEGIN TRANSACTION, COMMIT, ROLLBACK and SAVE TRANSACTION to control database changes.

Transactions are useful when multiple database operations need to be completed reliably and consistently.