SQL Mentoring Series — Part 2 of 3: Advanced Querying, Analytics and Transactional SQL
SQL Mentoring Series — Part 2 of 3Part 1: Relational Foundations and Core Querying · Part 2: Advanced Querying, Analytics and Transactional SQL · Part 3: Performance Engineering and Modern Data Architecture
A practical mentoring series for junior developers who want to move from correct SQL syntax to reliable database engineering.Part 1 of this course established the relational foundations: how to think about tables as representations of business reality, how to model entities and relationships, how to normalize data, how to choose sensible data types, how to evolve a schema safely, and how to retrieve and join data precisely.
Part 2 takes the next step.
This is where SQL stops feeling like a collection of SELECT statements and starts becoming a serious application and analytical language.
We are going to learn how to transform raw values into useful information, summarize millions of rows into trustworthy business measures, break difficult questions into manageable stages, compare rows with their neighbours and history, modify data safely, and protect business truth while many users and services work at the same time.
The six modules in this part are:
- Module 8 — Expressions, Functions, and Data Transformation
- Module 9 — Aggregation and Business Reporting
- Module 10 — Subqueries, CTEs, Views, and Derived Data
- Module 11 — Window Functions and Advanced Analytical SQL
- Module 12 — Modifying Data and Programmable SQL
- Module 13 — Transactions, Concurrency, and Consistency
The goal is to develop a production mindset.
At every stage, keep asking:
What does this value mean?
What does one row mean at this point in the query?
Am I preserving the correct business grain?
Could another user be changing this data at the same time?
Will this operation still be correct if it is retried?
If one step fails, what state will the database be left in?
These questions are what separate SQL that merely runs from SQL that can be trusted.
Module 8 — Expressions, Functions, and Data Transformation
Stored values are not always the values the business needs
A database stores facts in a form chosen for integrity, consistency, and reuse.
A report, API, or user interface often needs those facts in a different form.
An order line may store Quantity and UnitPrice, while the UI needs a line total.
A customer table may store FirstName and LastName, while a letter needs a display name.
A timestamp may store the precise time of an event, while a report needs the month.
A numeric risk score may be useful to a machine, while a manager wants the categories Low, Medium, and High.
Expressions and functions perform these transformations.
A simple arithmetic expression might be:
SELECT
Quantity,
UnitPrice,
Quantity * UnitPrice AS LineTotal
FROM OrderItems;The important thing is that LineTotal does not need to be physically stored simply because an application wants to display it. SQL can derive it from authoritative facts.
This leads to an important design habit:
Store facts when they are genuine independent facts. Derive values when they can be reliably calculated from other facts.
There are exceptions. Sometimes a calculated result is persisted deliberately for auditing, historical accuracy, or performance. But do not duplicate derived information without understanding why.
CASE: conditional thinking inside SQL
CASE allows a query to produce different values based on conditions.
For example:
SELECT
OrderId,
TotalAmount,
CASE
WHEN TotalAmount >= 5000 THEN 'High Value'
WHEN TotalAmount >= 1000 THEN 'Medium Value'
ELSE 'Standard'
END AS OrderBand
FROM Orders;Read this as a business rule.
If the order is at least 5,000, classify it as high value.
Otherwise, if it is at least 1,000, classify it as medium value.
Otherwise, classify it as standard.
CASE is extremely useful in reports, dashboards, sorting, grouped calculations, and data-cleaning queries.
But do not let a query become a hidden business-rules engine containing hundreds of nested conditions.
If the classification represents a major domain rule that changes frequently, consider whether it should live in a reference table, application domain logic, or another explicit configuration mechanism.
SQL can express business rules. That does not mean every business rule belongs inside one SQL statement.
Working with NULL
NULL is one of the most misunderstood values in SQL.
It does not mean zero.
It does not mean false.
It does not mean an empty string.
It means that the value is unknown, missing, or not applicable.
COALESCE is useful when you deliberately want a fallback value.
For example:
SELECT
CustomerId,
COALESCE(PreferredName, FirstName) AS DisplayName
FROM Customers;This says:
Use PreferredName when one exists. Otherwise use FirstName.
That is a meaningful fallback.
Now imagine:
SELECT AVG(COALESCE(DeliveryDays, 0))
FROM Orders;If DeliveryDays is null because the order has not yet been delivered, replacing it with zero means you are claiming the delivery took zero days.
That changes the business meaning.
The lesson is simple:
Do not use COALESCE merely to make null disappear. Use it when the replacement value is genuinely correct.
NULLIF performs a related but different task. It returns null when two expressions are equal.
A common defensive pattern is:
SELECT Revenue / NULLIF(OrderCount, 0)
FROM MonthlySummary;If OrderCount is zero, NULLIF turns it into null and prevents a divide-by-zero error.
Again, think about meaning. A null result now represents “this ratio cannot be calculated from these values.”
Casting and conversion
Data sometimes needs to move between types.
You may need to convert text to a date, an integer to a decimal, or a date-time to a date.
For example:
SELECT CAST(OrderedAt AS date)
FROM Orders;Casting is useful, but repeated conversion can also reveal poor schema design.
If a column stores dates as strings and every query must convert those strings to proper dates, the real problem may be the column type.
The best conversion is often the one you never need because the data was stored correctly in the first place.
Conversion can also affect performance.
Consider a date predicate written conceptually as:
WHERE YEAR(OrderedAt) = 2026It is readable, but SQL Server may need to apply the function to many stored values before determining which rows qualify.
A more searchable form is:
WHERE OrderedAt >= '2026-01-01'
AND OrderedAt < '2027-01-01'This brings us back to SARGability from Part 1.
A searchable predicate gives the optimizer a clearer opportunity to navigate an index.
The lesson from Module 8 is not “avoid functions.”
It is:
Use expressions and functions to transform values deliberately, while preserving business meaning and remaining aware of where transformations can interfere with efficient searching.
Module 9 — Aggregation and Business Reporting
Aggregation changes the grain
Operational databases contain detail.
Managers need summaries.
An Orders table may contain one row per order, but management asks:
How much did we sell?
How many customers purchased?
Which region performed best?
What is the average order value?
Aggregation turns detailed rows into summary rows.
Suppose we write:
SELECT SUM(TotalAmount) AS Revenue
FROM Orders;All qualifying order rows contribute to one total.
There is no GROUP BY, so the entire result becomes one group.
Now suppose we write:
SELECT
Region,
SUM(TotalAmount) AS Revenue
FROM Orders
GROUP BY Region;The result grain changes.
Before aggregation, one row represented one order.
After aggregation, one row represents one region.
That idea is central.
GROUP BY is not merely a syntax requirement. It defines what one output row represents.
If we group by Region and Status, one output row represents one region-and-status combination.
If we add Month, the result becomes one row per region, status, and month.
Every grouping column changes the grain.
COUNT is more subtle than it looks
COUNT(*) counts rows.
SELECT COUNT(*)
FROM Orders;If there are 100,000 orders, the answer is 100,000.
COUNT(DeliveredAt) counts only rows where DeliveredAt is not null.
That may answer a completely different question:
How many orders have a known delivery time?
COUNT(DISTINCT CustomerId) counts unique known customer IDs represented in the qualifying orders.
That does not necessarily tell you how many customers exist in the customer table. It tells you how many distinct customers appear in this order population.
The difference between rows, non-null values, and distinct entities is critical in reporting.
SUM, AVG, MIN, and MAX
SUM adds numeric values.
AVG calculates the arithmetic mean.
MIN returns the smallest value.
MAX returns the largest.
These functions look straightforward, but correct reporting still depends on the meaning of the input.
Most aggregate functions ignore nulls.
If three delivery durations are 2, 4, and null, the average of known durations is 3.
The null is not treated as zero.
That may be exactly what you want if the third order has not yet been delivered.
A report can be numerically correct and still be misleading if the business meaning of null is misunderstood.
WHERE versus HAVING
This is one of the most important aggregation distinctions.
WHERE filters individual rows before grouping.
HAVING filters completed groups after aggregation.
Suppose you want paid-order revenue by region:
SELECT
Region,
SUM(TotalAmount) AS Revenue
FROM Orders
WHERE Status = 'Paid'
GROUP BY Region;The unpaid rows never enter the groups.
Now suppose you only want regions whose paid revenue exceeds 100,000:
SELECT
Region,
SUM(TotalAmount) AS Revenue
FROM Orders
WHERE Status = 'Paid'
GROUP BY Region
HAVING SUM(TotalAmount) > 100000;Read that in business order:
Start with orders.
Keep only paid orders.
Group them by region.
Calculate revenue for each group.
Keep only groups above the threshold.
The logical processing order helps explain why these clauses behave differently.
Conditional aggregation
Sometimes one result row needs several measures.
For example, management may want one row per region showing total orders, paid orders, cancelled orders, and paid revenue.
A useful pattern is:
SELECT
Region,
COUNT(*) AS TotalOrders,
SUM(CASE WHEN Status = 'Paid' THEN 1 ELSE 0 END) AS PaidOrders,
SUM(CASE WHEN Status = 'Cancelled' THEN 1 ELSE 0 END) AS CancelledOrders,
SUM(CASE WHEN Status = 'Paid' THEN TotalAmount ELSE 0 END) AS PaidRevenue
FROM Orders
GROUP BY Region;Each detailed row contributes to one or more measures according to the conditions.
This is extremely useful for dashboards and management reports.
The join multiplication trap
This is one of the most important reporting mistakes a junior developer can learn to avoid.
Suppose an order has a total of £100 and four order-item rows.
After joining Orders to OrderItems, the result may contain four rows for that order.
The order-level TotalAmount appears four times because the grain is now one row per item.
If you calculate:
SUM(o.TotalAmount)you may get £400.
SQL has not made a mistake.
You gave it four rows containing £100 and asked it to add them.
The mistake happened before aggregation when the join changed the grain.
Do not automatically use DISTINCT to hide this.
Instead, ask:
What does one row represent now?
At what grain is the measure stored?
Should I aggregate the child table first?
Should I calculate revenue from item quantity and price rather than from the repeated parent amount?
Trustworthy reporting begins with grain.
Average of averages
Suppose Branch A has 10 orders averaging £100.
Branch B has 1,000 orders averaging £20.
The average of the two branch averages is £60.
But the overall average order value is not £60.
Branch A produced £1,000 of revenue.
Branch B produced £20,000.
Together that is £21,000 over 1,010 orders, giving an overall average of roughly £20.79.
The value £60 answers:
What is the average of the two branch-level averages if both branches have equal weight?
The value £20.79 answers:
What is the average individual order across all orders?
Neither is inherently wrong. They answer different questions.
A senior reporting habit is to retain counts and totals so that summaries can later be combined correctly.
ROLLUP, GROUPING SETS, and CUBE
Normal GROUP BY returns the exact grouping level requested.
ROLLUP adds hierarchical subtotals and a grand total.
For example:
GROUP BY ROLLUP (Region, Status)conceptually produces:
- Region and status totals
- Region subtotals
- One grand total
ROLLUP (Region, Status) rolls from region-and-status to region to everything.
ROLLUP (Status, Region) rolls from status-and-region to status to everything.
GROUPING SETS gives precise control:
GROUP BY GROUPING SETS
(
(Region, Status),
(Region),
()
)This explicitly requests region-and-status detail, region subtotals, and a grand total.
CUBE generates all grouping combinations for the supplied dimensions.
These features are powerful in analytical reporting, but use them because the business requires those summary levels, not simply because the syntax exists.
Stream Aggregate and Hash Aggregate
At the logical level, you say:
Group these rows.
Physically, SQL Server must implement the grouping somehow.
A Stream Aggregate works well when rows arrive ordered by the grouping keys. SQL Server can finish one group and move to the next.
A Hash Aggregate can accept unordered rows by maintaining an in-memory hash entry for each group.
Neither is universally better.
If an index already provides useful ordering, Stream Aggregate may be efficient.
If the data is unordered and sorting millions of rows would be expensive, Hash Aggregate may be attractive.
Sorting and hashing require memory. If SQL Server receives insufficient memory for the operation, it may spill intermediate work to tempdb, increasing I/O and slowing execution.
This is our first major bridge from logical SQL into performance engineering.
The report may be logically correct, but the physical method used to produce it still matters.
Module 10 — Subqueries, CTEs, Views, and Derived Data
Complex questions contain smaller questions
Consider:
Which customers have spent more than the average customer?
This is not one simple operation.
First, calculate spending per customer.
Second, calculate the average of those customer totals.
Third, compare each customer with that average.
SQL gives us several ways to express intermediate results.
Scalar subqueries
A scalar subquery returns one value.
For example:
SELECT OrderId, TotalAmount
FROM Orders
WHERE TotalAmount >
(
SELECT AVG(TotalAmount)
FROM Orders
);The inner query returns one average value.
The outer query compares every order against that value.
This is a clean example of a smaller question answering part of a larger question.
Multi-row subqueries and EXISTS
A subquery may return several values.
IN can test whether a value belongs to the returned set.
EXISTS is often even clearer when the real question is whether a relationship exists.
SELECT c.CustomerId, c.CustomerName
FROM Customers AS c
WHERE EXISTS
(
SELECT 1
FROM Orders AS o
WHERE o.CustomerId = c.CustomerId
);This means:
Return the customer if at least one related order exists.
The 1 is not important data. It simply emphasizes that the selected value inside the subquery is irrelevant. We care about existence.
NOT EXISTS asks the opposite:
Return customers for whom no related order exists.
This is one of the most useful anti-relationship patterns in SQL.
Be careful with NOT IN when null can appear in the inner result. SQL's three-valued logic can produce surprising outcomes. NOT EXISTS often communicates the intended logic more safely.
Correlated versus non-correlated subqueries
A non-correlated subquery can run independently.
The average-order-value subquery above does not need the current outer row.
A correlated subquery refers to a value from the outer row.
Conceptually, it asks:
For this customer, does a paid order exist?
Then:
For the next customer, does a paid order exist?
This is a logical explanation. Do not assume SQL Server literally runs a complete separate query for every outer row. The optimizer may transform the expression into a join or another physical plan.
Again, separate logical meaning from physical execution.
Derived tables
A derived table is a query used inside FROM.
SELECT CustomerId, Spending
FROM
(
SELECT
CustomerId,
SUM(TotalAmount) AS Spending
FROM Orders
GROUP BY CustomerId
) AS CustomerTotals
WHERE Spending > 10000;The inner query creates a new grain: one row per customer.
The outer query works against that derived dataset.
This is a powerful mental model:
A query result is itself relational data and can become the input to another query.
Common table expressions
A CTE gives a name to a query expression for one statement.
WITH CustomerTotals AS
(
SELECT
CustomerId,
SUM(TotalAmount) AS Spending
FROM Orders
GROUP BY CustomerId
)
SELECT CustomerId, Spending
FROM CustomerTotals
WHERE Spending > 10000;The CTE often improves readability because the intermediate result has a meaningful name.
Several CTEs can turn one complicated statement into a sequence of understandable stages.
For example:
First create PaidOrders.
Then create CustomerTotals.
Then return HighValueCustomers.
This style can be very effective when mentoring junior developers because each stage has a clear grain and purpose.
But there is an important correction:
A normal CTE is not automatically a physically stored temporary result.
It is mainly a named query expression.
Replacing a derived table with a CTE does not automatically improve performance.
Choose it for clarity first. Measure performance separately.
Recursive CTEs
A recursive CTE refers to itself.
It is useful for hierarchical data such as:
Employees and managers.
Folders and parent folders.
Categories and subcategories.
Product assemblies and components.
A recursive CTE has an anchor member and a recursive member.
The anchor finds the starting level.
The recursive member finds the next level.
The process continues until no more rows are produced.
A termination condition matters. Recursive logic that never stops is not clever; it is broken.
Views
A view stores a reusable query definition.
Applications can query it similarly to a table.
A normal view does not usually store a permanent separate copy of the rows. It exposes a virtual dataset produced from the underlying tables when queried.
Views can:
Simplify complicated joins.
Create stable read interfaces.
Expose only approved columns.
Centralize shared definitions.
But views can also hide complexity.
A view based on another view, based on several more views, may create an enormous underlying statement that few developers understand.
Use views as intentional interfaces, not as layers of fog.
The overall lesson from Module 10 is:
Break complex questions into understandable relational stages, but understand the lifetime and physical behaviour of each structure you choose.
Module 11 — Window Functions and Advanced Analytical SQL
GROUP BY collapses; window functions enrich
Window functions are one of the most useful features in advanced SQL.
Suppose we have three orders for one customer.
We want to show all three orders, but beside each order we also want the customer's total spending.
GROUP BY CustomerId would collapse the three orders into one customer row.
A window function keeps all three orders:
SELECT
OrderId,
CustomerId,
TotalAmount,
SUM(TotalAmount) OVER
(
PARTITION BY CustomerId
) AS CustomerTotal
FROM Orders;Every order remains visible.
The customer total is added beside it.
That gives us the key distinction:
GROUP BY changes the result grain.
A window function normally preserves the result grain while adding context.
PARTITION BY
PARTITION BY divides rows into independent calculation groups.
If we partition by CustomerId, every customer has a separate calculation context.
Without PARTITION BY, the whole qualifying result may be treated as one partition.
For example:
COUNT(*) OVER ()can place the overall row count beside every returned row.
ORDER BY inside a window
Some window calculations depend on sequence.
A running total needs to know which rows come first.
SUM(TotalAmount) OVER
(
PARTITION BY CustomerId
ORDER BY OrderedAt
)For each customer, the running total grows as orders progress through time.
The ORDER BY inside OVER controls calculation sequence.
The final ORDER BY of the query controls presentation.
These are different responsibilities.
ROW_NUMBER, RANK, and DENSE_RANK
ROW_NUMBER assigns a unique sequence.
It is useful for finding one preferred row per group, such as the latest order for each customer.
A common pattern is:
WITH RankedOrders AS
(
SELECT
OrderId,
CustomerId,
OrderedAt,
ROW_NUMBER() OVER
(
PARTITION BY CustomerId
ORDER BY OrderedAt DESC, OrderId DESC
) AS RowNumber
FROM Orders
)
SELECT *
FROM RankedOrders
WHERE RowNumber = 1;The tie-breaker matters.
If two orders share the same timestamp, OrderId DESC makes the result deterministic.
RANK gives tied values the same rank and leaves gaps afterward.
DENSE_RANK gives tied values the same rank without gaps.
Which is correct depends on the business interpretation of ranking.
LAG and LEAD
LAG looks backward.
LEAD looks forward.
They are excellent for time-series and event analysis.
You can ask:
What was the previous month's revenue?
How much did this balance change from the previous event?
How long until the next appointment?
Did the status change from the previous record?
Before window functions, developers often solved these questions using self-joins or correlated subqueries. LAG and LEAD usually express the intention more directly.
Window frames
A partition may contain thousands of rows, but a calculation may need only part of that partition.
That part is the frame.
A running total can be expressed as:
SUM(TotalAmount) OVER
(
PARTITION BY CustomerId
ORDER BY OrderedAt
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)A moving seven-row average might use:
AVG(DailySales) OVER
(
ORDER BY SalesDate
ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
)But seven rows are not automatically seven calendar days.
If the source table contains no row for a day with no sales, seven rows may cover more than seven days.
Sometimes the correct approach is to create or join to a calendar dataset that guarantees one row per day.
The wider lesson is:
Window functions are mathematically powerful, but the business interpretation still depends on the grain and completeness of the underlying data.
Performance considerations
Window functions often require rows to be ordered by partition and sort keys.
If an appropriate index supplies that order, SQL Server may avoid an expensive sort.
If not, the query may need a Sort operator.
Large sorts consume memory.
Insufficient memory can lead to spills to tempdb.
Window functions therefore connect analytical expressiveness with the physical concerns we will study much more deeply in Part 3.
Module 12 — Modifying Data and Programmable SQL
Writes deserve more caution than reads
A bad SELECT may return the wrong rows.
A bad UPDATE may permanently change every row.
That difference should affect how you work.
SQL modifies sets.
This statement:
UPDATE Orders
SET Status = 'Expired'
WHERE ExpiresAt < SYSUTCDATETIME();does not represent a loop.
It declares that every qualifying row belongs to the set that must change.
The safest habit before a significant update or delete is to run the predicate as a SELECT.
Inspect the target rows.
Check the count.
Then perform the modification.
INSERT
Always name the target columns:
INSERT INTO Customers
(
CustomerName,
EmailAddress
)
VALUES
(
@CustomerName,
@EmailAddress
);This makes the statement clearer and less dependent on physical column order.
SQL can also insert from another query:
INSERT INTO ArchivedOrders
(
OrderId,
CustomerId,
TotalAmount
)
SELECT
OrderId,
CustomerId,
TotalAmount
FROM Orders
WHERE OrderedAt < '2020-01-01';Set-based operations can move large populations without loading them into application memory one row at a time.
UPDATE
An update has three questions:
Which rows qualify?
Which columns change?
What values should replace the current values?
UPDATE Customers
SET EmailAddress = @Email
WHERE CustomerId = @CustomerId;The predicate is often the most dangerous part.
Application code should inspect the affected-row count.
If an operation expected to update one row updates zero, something may have changed.
If it updates many rows, the predicate may be wrong.
Atomic conditional updates
One of the most important production patterns is to combine the business condition and the modification.
Do not always:
Read stock.
Check it in C#.
Calculate a new number.
Write the number later.
Another request may change the stock between the read and write.
Instead:
UPDATE Inventory
SET AvailableQuantity = AvailableQuantity - @Quantity
WHERE ProductId = @ProductId
AND AvailableQuantity >= @Quantity;Then check the affected-row count.
One row means the reservation succeeded.
Zero rows means the condition was no longer true.
This pattern is useful for stock, balances, available credit, status transitions, and job claiming.
DELETE and soft deletion
DELETE removes rows.
Foreign keys may correctly prevent deletion of a parent row that still has dependent children.
Do not view referential integrity as an inconvenience. It is the database protecting the model.
Some systems use soft deletion:
UPDATE Customers
SET IsDeleted = 1
WHERE CustomerId = @CustomerId;This preserves the physical row, which can help with audit or recovery requirements.
But soft deletion creates complexity.
Every normal query must correctly exclude deleted rows.
Unique constraints may need special treatment.
Indexes can become larger.
Relationships must define what deletion actually means.
Use soft deletion because the business needs retained history, not simply because physical deletion feels scary.
Stored procedures, functions, and triggers
Stored procedures package controlled database operations.
They can be useful when several set-based statements belong close to the data and must execute as one operation.
Functions return values or datasets and can be useful for reusable calculations.
Triggers run automatically in response to data changes.
Triggers should be treated carefully because they hide work behind ordinary statements.
A developer may issue one UPDATE, while a trigger quietly updates other tables.
Triggers also need to be set-based. An update statement may affect one row or thousands.
Do not write trigger logic assuming only one row will ever change.
Temporary tables
A temporary table physically stores intermediate results for a limited scope.
It can be useful when:
An expensive intermediate result must be reused several times.
You need indexes on an intermediate dataset.
Breaking one huge query into stages gives the optimizer better information.
You need to inspect or manipulate a staged result.
Do not automatically replace every CTE with a temp table. Temporary structures also require writes, reads, allocation, and often tempdb resources.
Choose them when physical materialization actually helps.
Dynamic SQL
Dynamic SQL is sometimes necessary when statement structure changes at runtime.
Values should remain parameterized.
Do not concatenate raw user input into SQL.
Parameterized values protect against SQL injection and often improve plan reuse.
Object names such as table or column names cannot normally be supplied as ordinary value parameters. If the user can choose a sort column, map that choice to a fixed application-controlled allow-list rather than inserting arbitrary text.
Error handling
Database code should not hide failures.
A stored procedure may use TRY...CATCH, roll back the active transaction, record essential context, and THROW the error back to the caller.
The caller needs to know whether the operation succeeded.
A swallowed error is often more dangerous than a visible one because the application may continue as though the business operation completed.
The lesson from Module 12 is:
A data modification should have an exact target, a clear business condition, an observable outcome, and a safe failure path.
Module 13 — Transactions, Concurrency, and Consistency
A transaction represents one complete business operation
Imagine transferring £500 from Account A to Account B.
The debit succeeds.
The credit fails.
Money has disappeared from the represented business reality.
The two operations therefore belong in one transaction.
BEGIN TRANSACTION;
-- Debit account A
-- Credit account B
COMMIT;If something fails:
ROLLBACK;The transaction boundary should match the business boundary.
Do not automatically wrap an entire web request in one database transaction.
Keep the transaction around the smallest set of database changes that must succeed or fail together.
ACID
ACID gives us four useful ideas.
Atomicity means all or nothing.
Consistency means the transaction moves the database from one valid state to another.
Isolation controls how concurrent transactions interact.
Durability means committed work is preserved.
These ideas sound academic until you build a real system handling payments, stock, approvals, or legal records.
Then they become practical engineering requirements.
Keep transactions short
A transaction may hold locks, retain row versions, generate transaction-log activity, and occupy resources.
Do not begin a transaction and then:
Wait for a human.
Send an email.
Call a payment provider.
Generate a large report.
Wait for another web API.
Validate information that could have been validated earlier.
The preferred sequence is:
Validate what you can first.
Begin the transaction.
Perform the necessary database changes.
Commit.
Then perform external follow-up work using an appropriate reliability pattern.
Locks
A lock protects a resource.
A shared-style lock may allow other readers.
A write normally requires stronger exclusive protection.
Locking is not inherently bad.
It is one of the mechanisms that prevents concurrent operations from damaging each other's work.
The real question is how long the locks are held and how much data is protected.
Blocking
Blocking occurs when one transaction must wait because another transaction holds an incompatible lock.
Transaction A updates Order 500.
Transaction B also tries to update Order 500.
Transaction B waits until A commits or rolls back.
This is ordinary coordination.
Blocking becomes a performance problem when it lasts too long or affects too many sessions.
Long transactions, missing indexes, large scans, and poorly designed update patterns can make blocking much worse.
Deadlocks
A deadlock is different from ordinary blocking.
Transaction A holds Resource 1 and waits for Resource 2.
Transaction B holds Resource 2 and waits for Resource 1.
Neither can continue.
SQL Server detects the cycle and chooses one transaction as the victim.
That transaction is rolled back.
The application should be prepared to retry a safe complete business transaction after a deadlock.
You can reduce deadlocks by:
Keeping transactions short.
Accessing resources in a consistent order.
Using appropriate indexes.
Updating only the rows required.
Avoiding external calls while locks are held.
A retry is not a substitute for understanding the deadlock. It is a resilience mechanism while you still investigate the underlying access pattern.
Dirty reads
A dirty read sees unfinished work.
Transaction A changes a balance from £1,000 to £500 but has not committed.
Transaction B reads £500.
Transaction A later rolls back.
The official value returns to £1,000.
Transaction B saw a value that never became committed business reality.
This is why casually using READ UNCOMMITTED or NOLOCK for important operational data is dangerous.
Non-repeatable reads
Transaction B reads a committed balance of £1,000.
Transaction A updates it to £500 and commits.
Transaction B reads the same row again and sees £500.
Both values were committed when observed, but the same row changed during Transaction B's work.
That is a non-repeatable read.
Phantom reads
Transaction B asks:
How many unpaid invoices exceed £1,000?
It gets 10 rows.
Transaction A inserts another qualifying invoice and commits.
Transaction B repeats the search and gets 11 rows.
The result set gained a new qualifying row.
That is a phantom.
The distinction is worth remembering:
Dirty read: unfinished data.
Non-repeatable read: an existing row changed.
Phantom read: the membership of the qualifying set changed.
Isolation levels
Isolation levels decide how much concurrent change one transaction is allowed to observe.
READ UNCOMMITTED offers weak isolation and can allow dirty reads.
READ COMMITTED prevents dirty reads but normally allows later statements to observe newer committed changes.
REPEATABLE READ gives stronger protection to rows already read.
SERIALIZABLE provides the strongest traditional isolation and protects the qualifying range more aggressively.
Do not simply choose the strongest level everywhere.
Greater isolation may mean more blocking or more transaction conflicts.
Choose enough isolation to protect the business invariant.
Exact implementation details differ between database products, so platform-specific behaviour must always be checked.
Optimistic concurrency: a practical application extension
The source material teaches transactions, isolation, locking, and related anomalies. In our course, we extended that foundation into modern ASP.NET Core and EF Core application design.
Imagine a user loads Order 500 into Angular.
The row is version 12.
While they are editing, another user updates the order. The row becomes version 13.
The first user later saves their old copy.
Without concurrency protection, they may overwrite newer information.
SQL Server's rowversion can be used as an optimistic concurrency token.
Conceptually, the update becomes:
UPDATE Orders
SET Status = @Status
WHERE OrderId = @OrderId
AND RowVersion = @OriginalRowVersion;If the row is still at the original version, the update succeeds.
If another transaction changed it, zero rows match.
EF Core can detect that zero-row update as a concurrency conflict.
The API can return HTTP 409 Conflict.
The UI can reload the current state rather than silently overwriting another user's work.
Optimistic concurrency means:
Do not lock the row for the entire time a person is editing it.
Assume conflicts are relatively uncommon.
Detect the conflict at save time.
Atomic SQL for scarce resources
Optimistic concurrency is useful for user edits, but stock and balances often need atomic business conditions.
Use:
UPDATE Inventory
SET AvailableQuantity = AvailableQuantity - @Quantity
WHERE ProductId = @ProductId
AND AvailableQuantity >= @Quantity;Do not rely only on a C# read followed by a later write.
The database should evaluate the condition at the moment of change.
Idempotency: protecting against repeated commands
Distributed applications retry.
Suppose the API successfully processes a payment, but the network connection fails before the client receives the success response.
The client retries.
Without protection, the payment may be created twice.
Idempotency means repeating the same command does not create an additional business effect.
A common design is to give important commands a unique CommandId.
The database stores that ID under a unique constraint.
If the same command arrives again, the system recognizes that it was already processed and returns the previous outcome rather than repeating the business operation.
Do not confuse idempotency with optimistic concurrency.
Optimistic concurrency asks:
Has this record changed since I read it?
Idempotency asks:
Has this exact command already been processed?
The transactional outbox: reliable messaging after commit
Now imagine approving an order must also publish OrderApproved to Azure Service Bus.
If you commit SQL first and then publish, the process could crash after the commit but before the message.
The order is approved, but downstream services never hear about it.
If you publish first and then SQL fails, downstream services may believe in an approval that never committed.
The transactional outbox solves this by writing both the business change and an outbox record in the same local database transaction.
The order changes.
The outbox row records that OrderApproved needs to be published.
Both commit together.
A background worker later reads unpublished outbox rows and sends them to the messaging system.
If publication fails, it retries.
Because delivery may happen more than once, message consumers should also be idempotent.
This pattern gives the relational transaction one clear authority while supporting reliable asynchronous integration.
Bringing Part 2 Together
The six modules in Part 2 form one continuous engineering story.
Module 8 taught us to transform stored values into useful information without casually changing their meaning.
Module 9 taught us to summarize detailed rows into trustworthy business measures while protecting grain and avoiding double counting.
Module 10 taught us to break complex questions into smaller relational stages using subqueries, derived tables, CTEs, and views.
Module 11 taught us how to compare rows with related rows while preserving the original detail.
Module 12 taught us how to modify exact sets of rows safely and how to use programmable database features deliberately.
Module 13 placed all of that inside a concurrent production environment where multiple users and services may act at the same time.
The progression can be remembered like this:
Transform the data.
Summarize the data.
Organize complicated logic.
Compare rows across context and time.
Change the data deliberately.
Protect the change while the world is changing around you.
That is a substantial step beyond beginner SQL.
A developer who genuinely understands these ideas can participate in serious conversations about reporting accuracy, concurrency failures, API retries, transactional integrity, database responsibilities, and production data behaviour.
A Practical Mentoring Scenario
Imagine you are building an order-management system.
The business wants a dashboard showing revenue by region and order status.
You first identify the input grain: one row per order.
You use conditional aggregation rather than several separate queries.
You make sure you do not join to order items and accidentally repeat the order total.
The business then asks for regional subtotals and a company total.
You introduce ROLLUP or explicit GROUPING SETS.
The dashboard now needs each day's revenue and a seven-day rolling average.
You first aggregate sales to one row per calendar day. Then you apply a window function over that daily grain.
A customer service screen needs the latest order for each customer.
You use ROW_NUMBER partitioned by customer and ordered by the order date plus a deterministic tie-breaker.
The operations team needs to approve an order and reserve stock.
You do not load stock into C#, subtract it, and save later. You use an atomic conditional update.
The order status, stock reservation, audit row, and outbox message must succeed together, so you put the database changes in one short transaction.
Two administrators might edit the same order. You use optimistic concurrency to detect a stale save.
The browser might retry a request after a lost network response. You use an idempotency key so the same command cannot create the business effect twice.
The approved order must eventually reach another service. You use the transactional outbox so the database commit and the promise to publish an event are recorded atomically.
This one scenario connects nearly every idea in Part 2.
That is how you should study these modules.
Do not treat GROUP BY, ROW_NUMBER, transactions, and idempotency as unrelated interview terms.
Think of them as tools solving different parts of one production system.
Questions a Junior Developer Should Be Able to Answer After Part 2
You should be able to explain why replacing null with zero may change business meaning.
You should know the difference between WHERE and HAVING.
You should be able to state what one output row represents after a GROUP BY.
You should understand why joining a parent table to several child rows can inflate totals.
You should know why an average of group averages may differ from the overall average.
You should understand the purpose of ROLLUP, GROUPING SETS, and CUBE.
You should be able to explain the difference between Stream Aggregate and Hash Aggregate at a conceptual level.
You should know the difference between a scalar subquery, correlated subquery, derived table, CTE, recursive CTE, and view.
You should understand that a CTE is not automatically materialized.
You should be able to explain why EXISTS is often better suited to an existence question than a join that multiplies rows.
You should know the difference between GROUP BY and a window function.
You should be able to explain PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and a window frame.
You should understand why deterministic ordering matters.
You should know how to make a stock update atomic.
You should understand why a trigger must handle sets rather than assume one changed row.
You should know why dynamic SQL values should be parameterized.
You should be able to explain the meaning of a transaction boundary.
You should know the difference between a lock, blocking, and a deadlock.
You should know the difference between dirty, non-repeatable, and phantom reads.
You should understand why stronger isolation is not automatically better.
You should be able to explain optimistic concurrency, idempotency, and the outbox pattern as three different protections.
If you can explain these ideas in plain English rather than reciting definitions, you are developing the right level of understanding.
Suggested Practice Project for Junior Developers
Extend the order-management database from Part 1.
Start with Customers, Products, Orders, and OrderItems.
Add an Inventory table, an OrderAudit table, and an OutboxMessages table.
Then work through the following exercises.
Create a query that classifies orders into several value bands using CASE.
Calculate revenue by region.
Calculate paid and cancelled counts in the same grouped query.
Add a report using ROLLUP to show regional subtotals and a grand total.
Create a query that finds customers whose total spending exceeds the average customer spending.
Rewrite part of that logic using a CTE and explain why the CTE improves readability without claiming that it automatically improves performance.
Create a recursive employee or category hierarchy.
Use ROW_NUMBER to return the latest order for each customer.
Use LAG to compare one daily revenue total with the previous day.
Build a seven-day moving average and explain what happens when dates are missing.
Write an atomic inventory-reservation statement.
Wrap order approval, stock reservation, audit insertion, and outbox insertion inside one transaction.
Introduce a rowversion column and simulate two users attempting to update the same order.
Create an idempotency table with a unique command identifier.
Simulate a repeated API request and verify that the business operation happens only once.
Finally, explain every piece aloud as though mentoring another junior developer.
Ask yourself:
What is the grain?
What could another user change between these steps?
Which facts must commit together?
What happens when a network call is retried?
Where does the authoritative truth live?
This is how SQL moves from a query language to a production engineering skill.
Sources and Scope
This guide is a synthesis of the material used throughout Part 2 of the course.
The main source areas are:
- Josephine Bush, *Learn SQL Database Programming* — expressions, grouping and summarization, subqueries, common table expressions, transaction isolation, views, procedures, functions, triggers, temporary tables, and data modification.
- *SQL for Data Analytics*, 4th Edition — aggregate analysis, grouping sets, ordered analytics, window functions, window frames, rolling calculations, views, and analytical query design.
- Benjamin Nevarez, *SQL Server Query Tuning and Optimization* — used where Part 2 begins to touch physical implementation, particularly aggregation operators and the relationship between logical queries and physical plans.
Part 3 continues from here into SQL Server performance engineering: index architecture, query optimization, execution plans, statistics, plan caching, Query Store, large-scale analytical workloads, and modern data architecture.