Published / updated29 Aug 2026

Transactions, Indexes and Normalization

Before you start

You need: stored procedures (Article 08).

Time: about 55 minutes, plus the practice.

Learning objective

Wrap related changes in a correct transaction, add indexes that the query optimiser will actually use, and normalize a schema to third normal form.

Topics

• ACID and what a transaction guarantees
• BEGIN, COMMIT, ROLLBACK, XACT_ABORT
• Locking, blocking and deadlocks
• Isolation levels
• Clustered and nonclustered indexes
• Covering indexes and INCLUDE
• Reading an execution plan
• Normal forms, and when to stop

Why transactions

Recording a fee payment is two statements:

INSERT INTO dbo.FeePayment (...) VALUES (...);
UPDATE dbo.FeeAccount SET PaidAmount = PaidAmount + @Amount WHERE Id = @FeeAccountId;

If the server fails between them, the payment exists and the balance does not reflect it. The student appears to still owe money they have paid — and the receipt in their hand says otherwise.

A transaction makes the pair atomic: both happen, or neither does.

SET XACT_ABORT ON;

BEGIN TRANSACTION;

    INSERT INTO dbo.FeePayment (PublicId, SchoolId, FeeAccountId, Amount,
                                PaidOn, PaymentMode, CollectedBy)
    VALUES (NEWID(), @SchoolId, @FeeAccountId, @Amount,
            SYSUTCDATETIME(), @PaymentMode, @CollectedBy);

    UPDATE dbo.FeeAccount
    SET    PaidAmount = PaidAmount + @Amount
    WHERE  Id = @FeeAccountId AND SchoolId = @SchoolId;

COMMIT TRANSACTION;

ACID

Property	Means
Atomicity	All statements succeed, or none do
Consistency	Constraints hold before and after
Isolation	Concurrent transactions do not see each other's partial work
Durability	A committed change survives a crash

Writing transactions correctly

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- statements

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    THROW;
END CATCH

Four rules:

SET XACT_ABORT ON. Without it, some errors — a foreign key violation, a lock timeout — abort the statement but leave the transaction open. The connection returns to the pool holding locks, and the next request using it inherits an open transaction. This is the cause of mysterious blocking that clears when the application restarts.

Guard ROLLBACK with @@TRANCOUNT. Rolling back with no open transaction raises its own error, masking the real one.

Validate before BEGIN TRANSACTION. Every statement inside holds locks. Checks that can fail belong outside.

Keep transactions short. No user interaction, no external calls, no WAITFOR inside one. A transaction open while waiting for a web service blocks every other writer touching those rows.

Nested transactions are not real

BEGIN TRANSACTION;   -- @@TRANCOUNT = 1
BEGIN TRANSACTION;   -- @@TRANCOUNT = 2
COMMIT TRANSACTION;  -- @@TRANCOUNT = 1 — nothing is committed
ROLLBACK TRANSACTION;-- rolls back EVERYTHING, @@TRANCOUNT = 0

SQL Server counts nesting but does not honour it. An inner COMMIT only decrements the counter; only the outermost COMMIT writes. But any ROLLBACK discards the entire outermost transaction.

Use save points when you genuinely need partial rollback:

SAVE TRANSACTION BeforeResults;
-- ...
ROLLBACK TRANSACTION BeforeResults;   -- undoes only to this point

Locking and blocking

SQL Server locks rows it reads and writes so concurrent transactions do not corrupt each other.

Lock	Taken by	Compatible with
Shared (S)	Reads	Other shared locks
Exclusive (X)	Writes	Nothing
Update (U)	Read before a write	Shared

Blocking is normal: one transaction waits for another's lock. It becomes a problem when the holding transaction is long.

-- Who is blocking whom
SELECT session_id, blocking_session_id, wait_type, wait_time, last_wait_type
FROM   sys.dm_exec_requests
WHERE  blocking_session_id <> 0;

Deadlocks

Two transactions each hold a lock the other needs:

Transaction A: locks FeeAccount 42, then wants FeePayment 108
Transaction B: locks FeePayment 108, then wants FeeAccount 42

Neither can proceed. SQL Server detects this, kills one as the "deadlock victim", and returns error 1205.

The fix is consistent ordering. If every transaction touches FeeAccount before FeePayment, this deadlock cannot form. Deadlocks are almost always a code-ordering problem, not a database problem.

Since the victim is chosen arbitrarily, application code should retry on error 1205 — a deadlock is a transient failure, not a bug in the request.

Isolation levels

Level	Prevents	Cost
READ UNCOMMITTED	Nothing	Reads uncommitted "dirty" data
READ COMMITTED	Dirty reads	Default; readers block on writers
REPEATABLE READ	Non-repeatable reads	Holds shared locks to the end
SERIALIZABLE	Phantoms	Heavy locking
SNAPSHOT	All of the above	Uses tempdb for row versions

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

NOLOCK is not a performance setting

-- Do not do this
SELECT SUM(Amount) FROM dbo.FeePayment WITH (NOLOCK) WHERE SchoolId = 1;

WITH (NOLOCK) is READ UNCOMMITTED. It reads rows from transactions that may still roll back, and — less well known — it can skip rows entirely or read the same row twice if a page split occurs mid-scan.

On a fee report that means a total that is simply wrong, with no error and no way to detect it. It is widespread in legacy code because it makes blocking disappear. Use READ COMMITTED SNAPSHOT at the database level instead, which gives readers a consistent point-in-time view without blocking writers:

ALTER DATABASE NexCodingSchool SET READ_COMMITTED_SNAPSHOT ON;

Indexes

An index is a sorted structure letting SQL Server find rows without reading the whole table.

Clustered

One per table. It is the table, stored in key order.

CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (Id)

A good clustered key is narrow, unique, and ever-increasing — INT IDENTITY fits all three. A random GUID causes every insert to land mid-table, splitting pages and fragmenting the index.

Nonclustered

Up to hundreds per table. A separate structure holding the key columns plus a pointer back to the row.

CREATE NONCLUSTERED INDEX IX_Student_School_Class
ON dbo.Student (SchoolId, ClassName, Section);

Column order matters. This index serves:

WHERE SchoolId = 1
WHERE SchoolId = 1 AND ClassName = '10th'
WHERE SchoolId = 1 AND ClassName = '10th' AND Section = 'A'

but not:

WHERE ClassName = '10th'                     -- leading column missing
WHERE Section = 'A'

Think of a phone book sorted by surname then first name: useless for finding everyone called "Ravi".

Put the most selective and most frequently filtered column first. In a multi-tenant system that is almost always SchoolId, because every query filters on it.

Covering indexes

When an index contains every column a query needs, SQL Server never touches the table.

CREATE NONCLUSTERED INDEX IX_Student_School_Class
ON dbo.Student (SchoolId, ClassName, Section)
INCLUDE (RollNumber, Name, ParentPhone);

-- Fully covered — no lookup back to the table
SELECT RollNumber, Name, ParentPhone
FROM   dbo.Student
WHERE  SchoolId = 1 AND ClassName = '10th';

INCLUDE columns are stored at the leaf level only, so they do not enlarge the searchable part of the index. Use key columns for filtering and sorting, INCLUDE for columns merely returned.

Without covering, SQL Server does a key lookup per matching row. For a few rows that is fine; for thousands the optimiser gives up on the index and scans the table instead.

Filtered indexes

CREATE NONCLUSTERED INDEX IX_Student_Active
ON dbo.Student (SchoolId, ClassName, Section)
WHERE Status <> 1;

Smaller and faster when most queries only want active rows. The query's WHERE must include the same predicate for the optimiser to use it.

The cost of indexes

Every index must be updated on every INSERT, UPDATE of its columns, and DELETE. Indexes are not free:

• They slow writes
• They consume storage and memory
• Unused ones cost all of that and return nothing

Find unused indexes:

SELECT   OBJECT_NAME(s.object_id) AS TableName,
         i.name                   AS IndexName,
         s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM     sys.dm_db_index_usage_stats AS s
JOIN     sys.indexes AS i
      ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE    s.database_id = DB_ID()
  AND    i.is_primary_key = 0
ORDER BY s.user_updates DESC;

High user_updates with near-zero seeks and scans is an index that only costs. Note these counters reset when the service restarts, so judge them over a representative period.

Find missing ones:

SELECT   d.statement AS TableName,
         d.equality_columns, d.inequality_columns, d.included_columns,
         s.user_seeks, s.avg_user_impact
FROM     sys.dm_db_missing_index_details AS d
JOIN     sys.dm_db_missing_index_groups  AS g ON g.index_handle = d.index_handle
JOIN     sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle
ORDER BY s.avg_user_impact DESC;

Treat these as suggestions, not instructions. The optimiser proposes one index per query with no awareness of the others; applying every suggestion produces dozens of overlapping indexes that cripple writes. Consolidate them by hand.

Sargability, again

An index is only usable when the column stands alone:

-- Cannot seek
WHERE YEAR(PaidOn) = 2024
WHERE UPPER(Name) = 'RAVI KUMAR'
WHERE Name LIKE '%Kumar'
WHERE SchoolId + 0 = 1

-- Can seek
WHERE PaidOn >= '2024-01-01' AND PaidOn < '2025-01-01'
WHERE Name = 'Ravi Kumar'
WHERE Name LIKE 'Ravi%'
WHERE SchoolId = 1

The most common cause of an unused index is a function wrapped around the indexed column.

Reading an execution plan

Press Ctrl+M in SSMS to include the actual plan, then run the query.

Operator	Meaning
Index Seek	Good — jumps directly to matching rows
Index Scan	Reads the whole index
Clustered Index Scan	Reads the whole table
Table Scan	Whole heap, no clustered index
Key Lookup	Index found the row, table read for more columns
Nested Loops	Fine for small inputs
Hash Match	Normal for large joins and aggregates
Sort	Often removable with the right index

Read plans right to left, and check three things:

1. Thick arrows — many rows moving between operators.
2. A large gap between estimated and actual rows — stale statistics. Fix with UPDATE STATISTICS dbo.Student;
3. Key Lookup with a high row count — add the looked-up columns to an INCLUDE.

A scan is not automatically bad. Reading 90% of a small table is faster by scan than by seek plus lookups. Judge by row counts, not by operator name.

Normalization

Organising tables so each fact is stored once.

First normal form

Every column holds one atomic value; no repeating groups.

-- Violates 1NF
Student (Id, Name, Subjects)          -- 'Maths, Science, English' in one column

-- 1NF
Student        (Id, Name)
StudentSubject (StudentId, SubjectId)

A comma-separated column cannot be joined, filtered, indexed or constrained. It looks convenient and blocks every future query.

Second normal form

1NF, plus every non-key column depends on the whole composite key.

-- Violates 2NF: StudentName depends only on StudentId, not on ExamId
ExamResult (StudentId, ExamId, MarksObtained, StudentName)

-- 2NF
ExamResult (StudentId, ExamId, MarksObtained)
Student    (Id, Name)

Third normal form

2NF, plus no non-key column depends on another non-key column.

-- Violates 3NF: TeacherName depends on TeacherId, not on the Subject key
Subject (Id, Name, ClassName, TeacherId, TeacherName)

-- 3NF
Subject (Id, Name, ClassName, TeacherId)
Teacher (Id, Name)

Storing TeacherName on Subject means renaming Dr. Mehta requires updating every subject row, and missing one leaves the database contradicting itself.

3NF is the target for operational schemas. Higher forms exist and are rarely worth the complexity.

When to denormalize deliberately

Reporting sometimes justifies duplication — a nightly summary table, or a stored Outstanding on FeeAccount rather than summing payments each time.

Two rules:

• Denormalize only with measured evidence that the normalized version is too slow.
• Have one writer. A duplicated value updated from three places will drift, and then no one knows which number is right.

FeeAccount.PaidAmount in this schema is exactly such a denormalization: it duplicates SUM(FeePayment.Amount). It is justified because the balance is read constantly — and it is why every payment must update it inside the same transaction.

Errors you will hit

Message	Cause	Fix
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT	A path that neither commits nor rolls back	Every path must do one
The application hangs and no error appears	An uncommitted transaction is holding locks	SELECT @@TRANCOUNT; — it must be 0
Transaction (Process ID 62) was deadlocked	Two transactions each hold what the other needs	Access tables in the same order everywhere
Lock request time out period exceeded	Blocked by another session	sys.dm_exec_requests shows the blocker
An index made no difference	The query is not sargable, or the wrong columns	Read the execution plan
Inserts got slower after adding indexes	Every index must be maintained on write	Only index what you query

Never leave a transaction open in SSMS. It blocks the whole application, and the symptom looks like the API is broken.

Common mistakes

• No transaction around related writes
• No SET XACT_ABORT ON, leaving transactions open on the pooled connection
• ROLLBACK without checking @@TRANCOUNT
• Assuming nested transactions roll back independently
• Long transactions holding locks across user interaction or an external call
• WITH (NOLOCK) to make blocking go away, producing wrong totals
• Inconsistent table ordering across transactions, causing deadlocks
• No retry on error 1205
• Indexing without checking which queries actually run
• Wrong leading column, so the index is never used
• Applying every missing-index suggestion
• Functions wrapping indexed columns
• Judging a plan by operator name rather than row counts
• Comma-separated values in a column
• Denormalizing without measurement, or with several writers

Practice

The course assignments here are normalize a sample schema and use a transaction safely.

1. Take a flat table StudentExamFlat (StudentName, RollNumber, ClassName, Subjects, TeacherName, ExamName, Marks) and normalize it to 3NF. State which rule each step fixes.
2. Write usp_RecordFeePayment with XACT_ABORT, TRY...CATCH and a guarded ROLLBACK. Confirm the payment and balance update are atomic.
3. Force a failure between the two statements. Confirm the payment does not exist and the balance is unchanged.
4. Open two SSMS windows. In the first, BEGIN TRANSACTION and update a FeeAccount without committing. In the second, select that row. Observe the block. Query sys.dm_exec_requests from a third window to see it. Then commit.
5. Cause a deadlock deliberately: two windows updating FeeAccount and FeePayment in opposite order. Record error 1205 and which session was chosen as victim.
6. Fix it by making both windows touch the tables in the same order. Confirm no deadlock.
7. Insert 100,000 student rows. Run SELECT ... WHERE SchoolId = 1 AND ClassName = '10th' with Ctrl+M on and record the operator and duration.
8. Add IX_Student_School_Class. Re-run and compare. Note whether a Key Lookup appears.
9. Add INCLUDE (RollNumber, Name, ParentPhone). Re-run and confirm the lookup is gone.
10. Change the filter to WHERE ClassName = '10th' alone. Confirm the index is not used, and explain why.
11. Change it to WHERE YEAR(DateOfBirth) = 2010. Confirm the scan, then rewrite it as a sargable range.
12. Run the unused-index query against your database after exercising it.

Then run the course AI drill — compare two schema designs. Give an assistant the flat table from exercise 1 and ask for a normalized design. Check its answer for: a composite unique constraint scoped per school, correct nullability, DECIMAL for money, absent handling on results, and whether it invented a many-to-many where a one-to-many was correct.

You can now

• Write a transaction that leaves no partial state on any path
• Check @@TRANCOUNT before closing a query window
• Find a blocking session with sys.dm_exec_requests
• Add an index and prove from the plan that it changed the query
• Compare logical reads rather than elapsed time
• Normalize to 3NF and justify any denormalization

Review questions

1. Why does SET XACT_ABORT ON matter with connection pooling?
2. Why can WITH (NOLOCK) return a wrong total with no error?
3. Why does an index on (SchoolId, ClassName, Section) not help WHERE Section = 'A'?
4. What does a Key Lookup indicate, and how do you remove it?

Next: Guided database project