SQL Server and Relational Databases Masterclass: From Fundamentals to Production Performance

SQL Server and Relational Databases Masterclass: From Fundamentals to Production Performance

Let’s treat this like a serious one-to-one mentoring session.

You are not learning SQL as someone who has never seen a SELECT statement. You are a senior developer revising SQL properly, with the intention of becoming dangerous in a good way: able to design database tables, write clean queries, understand execution plans, review stored procedures, challenge bad data access code, and spot performance smells before they become production incidents.

A weak developer sees SQL as “that thing behind Entity Framework.”

A stronger developer sees SQL as the language of data shape, data integrity, performance, and business truth.

A database is not just storage. In many business systems, the database is the heart of the product. The API can be rewritten. The frontend can be redesigned. The cloud hosting can move. But the data often survives everything.

So let’s go from basics to expert level.

1. Relational database mental model

A relational database stores data in tables.

A table is like a well-organised spreadsheet, but stricter, safer, and far more powerful.

Imagine a loan management platform. You may have:

Customers
LoanApplications
LoanProducts
Payments
Documents
ApplicationStatusHistory

Each table represents a kind of thing. Each row represents one instance of that thing. Each column represents a specific attribute.

For example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

This says:

A customer has an ID. A customer must have a name. A customer must have an email. A customer has a creation date.

That NOT NULL matters. It is not decoration. It is a rule. It protects the data.

A senior developer does not just ask, “Can I store this?” A senior developer asks, “What rules must the database enforce so bad data cannot enter quietly?”

2. Tables, keys and relationships

A relational database becomes powerful when tables are connected.

A customer can have many loan applications.

CREATE TABLE LoanApplications
(
    LoanApplicationId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    RequestedAmount DECIMAL(18,2) NOT NULL,
    Status NVARCHAR(50) NOT NULL,
    SubmittedAt DATETIME2 NOT NULL,

    CONSTRAINT FK_LoanApplications_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

This foreign key says:

“You cannot create a loan application for a customer that does not exist.”

That is data integrity.

Without the foreign key, the application might accidentally create orphaned applications. Then reports break. Screens show missing names. Finance numbers become suspicious. Everyone blames the frontend. But the real problem is bad database design.

Primary key means identity of the row.

Foreign key means relationship to another table.

Unique constraint means no duplicates where duplicates should not exist.

Example:

ALTER TABLE Customers
ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);

But be careful. In real life, email uniqueness can be trickier. Do we allow the same email for joint applicants? Do we treat upper/lowercase differently? Do we support multiple tenants? A senior developer asks business questions before locking rules into the database.

3. Normalisation: avoid messy duplicated data

Normalisation means designing tables so data is stored cleanly, without unnecessary duplication.

Bad design:

CREATE TABLE LoanApplications
(
    LoanApplicationId INT PRIMARY KEY,
    CustomerName NVARCHAR(200),
    CustomerEmail NVARCHAR(320),
    LoanProductName NVARCHAR(200),
    LenderName NVARCHAR(200),
    RequestedAmount DECIMAL(18,2)
);

This may look simple, but it duplicates customer and loan product data in every application.

What happens when the customer changes email? What happens when the lender changes product name? Which row is the truth?

Better design:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL,
    Email NVARCHAR(320) NOT NULL
);

CREATE TABLE LoanProducts
(
    LoanProductId INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(200) NOT NULL,
    LenderName NVARCHAR(200) NOT NULL,
    InterestRate DECIMAL(5,2) NOT NULL
);

CREATE TABLE LoanApplications
(
    LoanApplicationId INT IDENTITY PRIMARY KEY,
    CustomerId INT NOT NULL,
    LoanProductId INT NOT NULL,
    RequestedAmount DECIMAL(18,2) NOT NULL,

    CONSTRAINT FK_Applications_Customers
        FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId),

    CONSTRAINT FK_Applications_Products
        FOREIGN KEY (LoanProductId) REFERENCES LoanProducts(LoanProductId)
);

Now each thing lives in its own table.

Normalisation reduces duplication and protects consistency.

But senior point: normalisation is not religion. Reporting systems sometimes denormalise for speed. Data warehouses use star schemas. Read models may deliberately store duplicated values to avoid expensive joins.

The judgement is:

Normalise for transactional integrity. Denormalise carefully for reporting/performance/read models.

4. SQL basics: selecting data properly

Basic SQL:

SELECT
    CustomerId,
    FullName,
    Email
FROM Customers;

Never casually use this in production application queries:

SELECT *
FROM Customers;

SELECT * is a smell because:

It returns columns you may not need. It increases network traffic. It can break code if column order changes. It hides the real data contract. It may include sensitive fields accidentally.

Better:

SELECT
    CustomerId,
    FullName,
    Email
FROM Customers;

Filtering:

SELECT
    LoanApplicationId,
    CustomerId,
    RequestedAmount,
    Status
FROM LoanApplications
WHERE Status = 'Submitted';

Sorting:

SELECT
    LoanApplicationId,
    RequestedAmount,
    SubmittedAt
FROM LoanApplications
WHERE Status = 'Submitted'
ORDER BY SubmittedAt DESC;

Pagination:

DECLARE @PageNumber INT = 1;
DECLARE @PageSize INT = 25;

SELECT
    LoanApplicationId,
    RequestedAmount,
    Status,
    SubmittedAt
FROM LoanApplications
ORDER BY SubmittedAt DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

A senior reviewer asks:

“Where is the ORDER BY?”

Without deterministic ordering—including a unique tie-breaker—pagination can behave unpredictably. Page 1 and page 2 may overlap or miss rows when data changes.

5. Joins: where many developers get into trouble

A join combines rows from multiple tables.

Example:

SELECT
    la.LoanApplicationId,
    c.FullName,
    c.Email,
    lp.ProductName,
    lp.LenderName,
    la.RequestedAmount,
    la.Status
FROM LoanApplications la
JOIN Customers c
    ON c.CustomerId = la.CustomerId
JOIN LoanProducts lp
    ON lp.LoanProductId = la.LoanProductId;

This gives application plus customer plus product.

Think of joins like asking several filing cabinets to cooperate. The application cabinet says, “I have CustomerId 10.” The customer cabinet says, “CustomerId 10 is Faz Ahmed.” The product cabinet says, “LoanProductId 5 is Bridging Loan.”

Common joins:

INNER JOIN means only matching rows. LEFT JOIN means keep left-side rows even if right-side data is missing.

Example:

SELECT
    c.CustomerId,
    c.FullName,
    la.LoanApplicationId
FROM Customers c
LEFT JOIN LoanApplications la
    ON la.CustomerId = c.CustomerId;

This returns customers even if they have no applications.

Senior review smell:

SELECT
    c.FullName,
    la.LoanApplicationId,
    p.PaymentId
FROM Customers c
JOIN LoanApplications la ON la.CustomerId = c.CustomerId
JOIN Payments p ON p.LoanApplicationId = la.LoanApplicationId;

This can multiply rows. One customer may have many applications, and each application may have many payments. If the developer expected one row per customer, this query is wrong.

Always ask:

“What is the intended grain of the result?”

Is this query returning one row per customer? One row per application? One row per payment? One row per status change?

If you do not know the grain, you do not understand the query.

6. Aggregation: counts, sums and business truth

Reporting often needs aggregation.

SELECT
    Status,
    COUNT(*) AS ApplicationCount
FROM LoanApplications
GROUP BY Status;

This returns counts by status.

Total requested amount by status:

SELECT
    Status,
    COUNT(*) AS ApplicationCount,
    SUM(RequestedAmount) AS TotalRequestedAmount
FROM LoanApplications
GROUP BY Status;

Filter aggregated results with HAVING:

SELECT
    CustomerId,
    COUNT(*) AS ApplicationCount
FROM LoanApplications
GROUP BY CustomerId
HAVING COUNT(*) > 3;

WHERE filters rows before grouping. HAVING filters groups after grouping.

A common mistake:

SELECT
    CustomerId,
    COUNT(*) AS ApplicationCount
FROM LoanApplications
WHERE COUNT(*) > 3
GROUP BY CustomerId;

That is wrong because COUNT(*) does not exist until after grouping.

A senior reviewer also checks whether the aggregation makes business sense. For example, do we count rejected applications? Cancelled ones? Drafts? Duplicate submissions? Test data? Deleted data?

SQL can produce accurate numbers for the wrong question. That is one of the most dangerous things in data work.

7. Data types: boring but extremely important

Bad data types create long-term pain.

Money should not be stored as FLOAT.

Bad:

RequestedAmount FLOAT NOT NULL

Better:

RequestedAmount DECIMAL(18,2) NOT NULL

FLOAT is approximate. It is for scientific-style approximate values, not finance.

Dates should not be stored as strings.

Bad:

SubmittedDate NVARCHAR(50)

Better:

SubmittedAt DATETIME2 NOT NULL

Status should not be random free text if values are controlled.

Bad:

Status NVARCHAR(50) NOT NULL

This allows:

Submitted
submitted
Submited
SUBMITTED
Under Review
under-review

Better options:

Use a lookup table. Use a constrained value. Use application enum plus database check constraint.

Example:

ALTER TABLE LoanApplications
ADD CONSTRAINT CK_LoanApplications_Status
CHECK (Status IN ('Draft', 'Submitted', 'UnderReview', 'Approved', 'Rejected'));

Now invalid statuses cannot enter.

Senior rule:

“Choose data types as if the database will outlive the application code, because often it will.”

8. Constraints: let the database protect itself

Application validation is good. Database constraints are stronger.

Imagine the API has a bug and sends requested amount as -5000.

Would the database allow it?

If there is no constraint, yes.

Better:

ALTER TABLE LoanApplications
ADD CONSTRAINT CK_LoanApplications_RequestedAmount_Positive
CHECK (RequestedAmount > 0);

Email required:

ALTER TABLE Customers
ALTER COLUMN Email NVARCHAR(320) NOT NULL;

Unique reference number:

ALTER TABLE LoanApplications
ADD CONSTRAINT UQ_LoanApplications_ReferenceNumber
UNIQUE (ReferenceNumber);

The senior mindset is not “validation belongs only in C#.” The senior mindset is “important business invariants should be protected at the right layers.”

Frontend validation improves user experience. Backend validation protects the API. Database constraints protect the data.

9. Indexes: the database version of an organised library

Imagine a library with one million books.

Without an index, to find “SQL Performance Tuning”, you walk shelf by shelf.

With an index, you look up the title and jump directly to the location.

Database indexes work similarly.

Suppose we often search applications by status and submission date:

SELECT
    LoanApplicationId,
    ReferenceNumber,
    RequestedAmount,
    Status,
    SubmittedAt
FROM LoanApplications
WHERE Status = 'Submitted'
ORDER BY SubmittedAt DESC;

Useful index:

CREATE INDEX IX_LoanApplications_Status_SubmittedAt
ON LoanApplications (Status, SubmittedAt DESC, LoanApplicationId DESC)
INCLUDE (ReferenceNumber, RequestedAmount);

The key columns help filtering and sorting.

The included columns help avoid extra lookups.

But indexes are not free.

They consume storage. They slow inserts, updates and deletes. They need maintenance. Too many indexes confuse and burden the system.

Senior rule:

“Create indexes to support real query patterns, not because a tool suggested one in isolation.”

10. Execution plans: seeing what SQL Server actually does

A query is not just text. SQL Server turns it into a plan.

The execution plan shows how SQL Server intends to retrieve data.

Important operators:

Index Seek usually means efficient targeted lookup. Index Scan means scanning an index. Not always bad, but suspicious for selective queries. Table Scan means scanning the table. Dangerous on large tables. Key Lookup means SQL found rows in an index, then went back to fetch missing columns. Fine for few rows, bad for many. Sort can be expensive. Hash Match can be normal for large joins/aggregations. Nested Loops can be good for small sets, bad for large repeated lookups.

Example code review question:

SELECT
    LoanApplicationId,
    ReferenceNumber,
    ApplicantName,
    RequestedAmount,
    Status,
    SubmittedAt
FROM LoanApplications
WHERE Status = 'Submitted'
ORDER BY SubmittedAt DESC;

Ask:

Is there an index on Status, SubmittedAt? Is SQL scanning the whole table? Are returned columns covered? How many rows are expected? Are statistics up to date?

A senior SQL reviewer does not guess performance. They ask for the actual execution plan, logical reads, duration, CPU, and row counts.

11. SARGability: write queries indexes can use

SARGable means the query can use an index efficiently.

Bad:

WHERE YEAR(SubmittedAt) = 2026

This applies a function to the column. SQL Server may not use the index properly.

Better:

WHERE SubmittedAt >= '2026-01-01'
  AND SubmittedAt < '2027-01-01'

Bad:

WHERE LOWER(Email) = 'faz@example.com'

Better: store normalised email or use appropriate collation/index strategy.

Bad:

WHERE ISNULL(Status, 'Draft') = 'Submitted'

Better:

WHERE Status = 'Submitted'

and design the column as NOT NULL if status is required.

Senior phrase:

“Do not wrap indexed columns in functions in the WHERE clause unless you understand the performance impact.”

12. Stored procedures: useful but dangerous when abused

Stored procedures are powerful.

Example:

CREATE PROCEDURE dbo.GetSubmittedLoanApplications
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        LoanApplicationId,
        ReferenceNumber,
        ApplicantName,
        RequestedAmount,
        Status,
        SubmittedAt
    FROM LoanApplications
    WHERE Status = 'Submitted'
    ORDER BY SubmittedAt DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

Good points:

SET NOCOUNT ON avoids extra row count messages. Parameters avoid SQL injection and improve reuse. Pagination is included. Columns are explicit.

Bad stored procedure smell:

CREATE PROCEDURE dbo.SearchApplications
    @SearchText NVARCHAR(100)
AS
BEGIN
    SELECT *
    FROM LoanApplications
    WHERE ApplicantName LIKE '%' + @SearchText + '%'
       OR ReferenceNumber LIKE '%' + @SearchText + '%'
       OR Status LIKE '%' + @SearchText + '%';
END;

Problems:

SELECT * Leading wildcard prevents normal index seek. Search across unrelated columns. No pagination. Potentially slow on large table. Unclear result contract.

A better search strategy may involve full-text search, separate filters, or at least pagination and explicit columns.

13. Transactions: protect consistency, but keep them short

A transaction means all operations succeed together or fail together.

Example:

BEGIN TRANSACTION;

UPDATE LoanApplications
SET Status = 'Approved'
WHERE LoanApplicationId = @ApplicationId;

INSERT INTO ApplicationStatusHistory
(
    LoanApplicationId,
    OldStatus,
    NewStatus,
    ChangedAt
)
VALUES
(
    @ApplicationId,
    'UnderReview',
    'Approved',
    SYSUTCDATETIME()
);

COMMIT TRANSACTION;

If one part fails, we should roll back.

Better with error handling:

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE LoanApplications
    SET Status = 'Approved'
    WHERE LoanApplicationId = @ApplicationId;

    INSERT INTO ApplicationStatusHistory
    (
        LoanApplicationId,
        OldStatus,
        NewStatus,
        ChangedAt
    )
    VALUES
    (
        @ApplicationId,
        'UnderReview',
        'Approved',
        SYSUTCDATETIME()
    );

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

    THROW;
END CATCH;

Senior rule:

“Transactions should protect consistency, but they should be as short as possible.”

Do not hold a database transaction while calling an external API, sending email, waiting for a file upload, or doing slow business workflow outside the database.

Long transactions cause locks. Locks cause blocking. Blocking causes angry users.

14. Isolation and locking: where production pain begins

When multiple users access data at the same time, the database must protect consistency.

Example:

Two underwriters approve the same loan application at the same time.

What happens?

You need to understand concurrency.

A simple update:

UPDATE LoanApplications
SET Status = 'Approved'
WHERE LoanApplicationId = @ApplicationId;

But what if it was already rejected by someone else?

Better:

UPDATE LoanApplications
SET Status = 'Approved'
WHERE LoanApplicationId = @ApplicationId
  AND Status = 'UnderReview';

IF @@ROWCOUNT = 0
BEGIN
    THROW 50001, 'Application cannot be approved from current status.', 1;
END;

This protects the state transition.

In EF Core, you may use row version:

ALTER TABLE LoanApplications
ADD RowVersion ROWVERSION NOT NULL;

Then EF can detect concurrency conflicts.

Senior concept:

“Concurrency is not rare in business systems. If two people can touch the same workflow, design for it.”

15. Views, CTEs and window functions

A view is a saved query.

CREATE VIEW dbo.vwLoanApplicationSummary
AS
SELECT
    la.LoanApplicationId,
    la.ReferenceNumber,
    c.FullName AS CustomerName,
    lp.ProductName,
    la.RequestedAmount,
    la.Status,
    la.SubmittedAt
FROM LoanApplications la
JOIN Customers c ON c.CustomerId = la.CustomerId
JOIN LoanProducts lp ON lp.LoanProductId = la.LoanProductId;

Views can simplify read queries, but do not assume views automatically improve performance. A view is often just query abstraction.

A CTE improves readability:

WITH SubmittedApplications AS
(
    SELECT
        LoanApplicationId,
        CustomerId,
        RequestedAmount,
        SubmittedAt
    FROM LoanApplications
    WHERE Status = 'Submitted'
)
SELECT
    CustomerId,
    COUNT(*) AS SubmittedCount,
    SUM(RequestedAmount) AS TotalRequested
FROM SubmittedApplications
GROUP BY CustomerId;

Window functions are excellent for analytical queries.

Example: rank applications by requested amount per customer.

SELECT
    CustomerId,
    LoanApplicationId,
    RequestedAmount,
    ROW_NUMBER() OVER (
        PARTITION BY CustomerId
        ORDER BY RequestedAmount DESC
    ) AS AmountRank
FROM LoanApplications;

Get latest application per customer:

WITH RankedApplications AS
(
    SELECT
        CustomerId,
        LoanApplicationId,
        Status,
        SubmittedAt,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerId
            ORDER BY SubmittedAt DESC
        ) AS RowNumber
    FROM LoanApplications
)
SELECT
    CustomerId,
    LoanApplicationId,
    Status,
    SubmittedAt
FROM RankedApplications
WHERE RowNumber = 1;

This is much cleaner than messy self-joins.

16. Temporary tables and table variables

Temporary tables can be useful for complex multi-step queries.

CREATE TABLE #SubmittedApplications
(
    LoanApplicationId INT,
    CustomerId INT,
    RequestedAmount DECIMAL(18,2)
);

INSERT INTO #SubmittedApplications
SELECT
    LoanApplicationId,
    CustomerId,
    RequestedAmount
FROM LoanApplications
WHERE Status = 'Submitted';

SELECT
    CustomerId,
    COUNT(*) AS ApplicationCount,
    SUM(RequestedAmount) AS TotalRequested
FROM #SubmittedApplications
GROUP BY CustomerId;

Temporary tables can have indexes and statistics. They are often useful for larger intermediate result sets.

Table variables can be useful for small sets, but historically they can produce poor estimates. Modern SQL Server has improved, but you still need judgement.

Senior rule:

“Use temporary objects when they simplify logic or improve performance, but test with realistic data volumes.”

17. Dynamic SQL: powerful but risky

Dynamic SQL builds SQL text at runtime.

Sometimes useful for flexible search.

Bad and dangerous:

SET @Sql = 'SELECT * FROM LoanApplications WHERE ApplicantName = ''' + @Name + '''';
EXEC(@Sql);

This risks SQL injection.

Better:

DECLARE @Sql NVARCHAR(MAX) =
    N'SELECT
          LoanApplicationId,
          ApplicantName,
          RequestedAmount,
          Status
      FROM LoanApplications
      WHERE 1 = 1';

IF @Status IS NOT NULL
    SET @Sql += N' AND Status = @Status';

EXEC sp_executesql
    @Sql,
    N'@Status NVARCHAR(50)',
    @Status = @Status;

Dynamic SQL should be parameterised with sp_executesql.

Senior point:

“Dynamic SQL is not automatically bad. Unsafe string concatenation is bad.”

18. Data warehouses and data science mindset

The user asked for world-class data scientist level, so let’s connect relational thinking to analytics.

Transactional databases are usually designed for day-to-day operations.

Example:

Create application. Approve application. Record payment. Update status.

This is OLTP: Online Transaction Processing.

Analytics systems are designed for reporting and analysis.

Example:

Total approvals by month. Average loan amount by lender. Conversion rate from submitted to approved. Default rate by product type.

This is OLAP: Online Analytical Processing.

For analytics, you may use fact and dimension tables.

Fact table:

FactLoanApplication

Dimensions:

DimCustomer
DimLender
DimProduct
DimDate
DimStatus

Example analytical query:

SELECT
    d.CalendarYear,
    d.MonthName,
    p.ProductType,
    COUNT(*) AS ApplicationCount,
    AVG(f.RequestedAmount) AS AverageRequestedAmount
FROM FactLoanApplication f
JOIN DimDate d ON d.DateKey = f.SubmittedDateKey
JOIN DimProduct p ON p.ProductKey = f.ProductKey
GROUP BY
    d.CalendarYear,
    d.MonthName,
    p.ProductType;

A data scientist must understand grain.

The grain of FactLoanApplication might be one row per application.

The grain of FactPayment might be one row per payment.

Mixing facts of different grain without care creates wrong numbers.

Senior analytical warning:

“Most bad dashboards are not caused by bad charts. They are caused by misunderstood data grain, unclear definitions, duplicate rows, and poor joins.”

19. Production mentoring case: reserve limited stock correctly

Imagine BuildEstate Pro now supports ordering materials. Two site managers can reserve the last pallet of insulation at the same time. The system must never make available stock negative, repeat a reservation after an uncertain retry, or hold locks while calling an external supplier.

Junior: I can SELECT AvailableQuantity, check it in C#, then UPDATE the row.

>

Senior: That works with one request. Under concurrency, two sessions can both read the same availability. Put the invariant at the database write boundary and prove it with competing transactions.

Start with explicit tables:

CREATE TABLE Inventory.StockItem
(
    ProductId          uniqueidentifier NOT NULL,
    WarehouseId        uniqueidentifier NOT NULL,
    OnHandQuantity     int NOT NULL,
    ReservedQuantity   int NOT NULL,
    RowVersion         rowversion NOT NULL,

    CONSTRAINT PK_StockItem
        PRIMARY KEY (WarehouseId, ProductId),

    CONSTRAINT CK_StockItem_NonNegative
        CHECK (OnHandQuantity >= 0 AND ReservedQuantity >= 0),

    CONSTRAINT CK_StockItem_ReservedWithinOnHand
        CHECK (ReservedQuantity <= OnHandQuantity)
);

CREATE TABLE Inventory.Reservation
(
    ReservationId      uniqueidentifier NOT NULL,
    RequestId          uniqueidentifier NOT NULL,
    WarehouseId        uniqueidentifier NOT NULL,
    ProductId          uniqueidentifier NOT NULL,
    Quantity           int NOT NULL,
    CreatedAtUtc       datetime2(3) NOT NULL,
    Status             varchar(20) NOT NULL,

    CONSTRAINT PK_Reservation PRIMARY KEY (ReservationId),
    CONSTRAINT UQ_Reservation_RequestId UNIQUE (RequestId),
    CONSTRAINT CK_Reservation_Quantity CHECK (Quantity > 0),
    CONSTRAINT CK_Reservation_Status
        CHECK (Status IN ('Active', 'Released', 'Fulfilled')),
    CONSTRAINT FK_Reservation_StockItem
        FOREIGN KEY (WarehouseId, ProductId)
        REFERENCES Inventory.StockItem (WarehouseId, ProductId)
);

The composite key expresses that stock is identified by warehouse and product. rowversion is an automatically generated binary concurrency token; it is not a timestamp or business version number. datetime2(3) records the chosen timestamp precision. The status check protects stored values, while workflow transitions still belong in application/domain logic or tightly controlled procedures.

The unique request ID creates a database-enforced idempotency boundary. Checking first in application code is useful for returning the prior result, but uniqueness handles concurrent duplicates.

20. Make the reservation update atomic

One safe shape updates only if enough stock remains:

UPDATE Inventory.StockItem
SET ReservedQuantity = ReservedQuantity + @Quantity
WHERE WarehouseId = @WarehouseId
  AND ProductId = @ProductId
  AND OnHandQuantity - ReservedQuantity >= @Quantity;

IF @@ROWCOUNT = 0
BEGIN
    -- Distinguish missing stock item from insufficient availability
    -- with a deliberate follow-up query/result policy.
END;

The predicate and increment occur in one statement under SQL Server's concurrency control. The check constraint is a final backstop. A transaction can then insert the reservation record:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE Inventory.StockItem
SET ReservedQuantity = ReservedQuantity + @Quantity
WHERE WarehouseId = @WarehouseId
  AND ProductId = @ProductId
  AND OnHandQuantity - ReservedQuantity >= @Quantity;

IF @@ROWCOUNT = 0
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, 'Stock is missing or insufficient.', 1;
END;

INSERT Inventory.Reservation
(
    ReservationId,
    RequestId,
    WarehouseId,
    ProductId,
    Quantity,
    CreatedAtUtc,
    Status
)
VALUES
(
    @ReservationId,
    @RequestId,
    @WarehouseId,
    @ProductId,
    @Quantity,
    @CreatedAtUtc,
    'Active'
);

COMMIT TRANSACTION;

Production code handles unique-key conflict for duplicate RequestId by loading the original reservation and checking that the request fingerprint/meaning matches. It also uses TRY/CATCH when cleanup/translation is required, preserves the original error where appropriate, and maps database errors to stable application outcomes rather than exposing message text.

Junior: Could I use SERIALIZABLE and keep the read-then-write code?

>

Senior: Possibly, but it expands locking/range-lock behaviour and deadlock risk. Prefer an atomic statement when it expresses the invariant directly. Choose isolation from the whole transaction's needs, not as a blanket cure.

21. Isolation levels describe allowed observations

Isolation is not simply “more is better.” It defines which concurrent effects a transaction may observe and what SQL Server must do to provide that behaviour.

Read Committed

The common default prevents reading uncommitted changes, usually through shared locks unless read-committed snapshot is enabled at the database level. Two statements in one transaction can still see different committed data.

Read Committed Snapshot

With READ_COMMITTED_SNAPSHOT enabled, readers under Read Committed commonly use row versions instead of shared locks. This can reduce reader/writer blocking, but it increases version-store usage and does not prevent write conflicts or make multi-statement business logic atomic.

Snapshot

Snapshot isolation provides transactionally consistent versioned reads and can detect update conflicts. It requires database configuration and understanding of version retention. It is not the same as the rowversion column type.

Repeatable Read and Serializable

Repeatable Read protects rows read from being changed before the transaction ends but does not necessarily prevent new rows matching a range. Serializable adds range protection and strongest conventional isolation, with more blocking/deadlock potential.

Read Uncommitted / NOLOCK

NOLOCK permits dirty/inconsistent observations and does not mean “no locks anywhere.” A report can read rolled-back data, miss rows or observe them inconsistently. Do not apply it to remove blocking without accepting and documenting those semantics.

Choose per workflow. A dashboard might use a readable replica or snapshot-based approach. A financial/stock command needs atomic invariants. Monitor the consequences.

22. Diagnose blocking before changing isolation

Blocking is normal when one session needs a resource held incompatibly by another. Harmful blocking occurs when waits exceed the workload's objective.

Capture:

• blocked and blocking session IDs;
• wait type and duration;
• current/last SQL text and plan;
• transaction start time;
• locks/resources involved;
• application/host/login context;
• parameter values where safely available.

Find the head blocker. Killing a random blocked request treats the victim. A sleeping session with an open transaction may be the real cause.

Typical causes:

• transactions wrap user interaction or remote calls;
• a missing index scans/locks many rows;
• an update touches rows in inconsistent order;
• large batch changes run as one transaction;
• application error paths fail to commit/rollback/dispose;
• reporting shares the primary OLTP workload inappropriately.

Fix transaction scope and query access first. Enabling snapshot semantics can reduce read/write blocking but requires capacity, testing and conflict understanding. Adding NOLOCK trades waiting for untrustworthy reads.

23. Deadlocks are cycles, not merely slow queries

A deadlock occurs when sessions hold resources the others need in a cycle. SQL Server chooses a victim and rolls back its transaction.

Example:

Transaction A: locks StockItem, then requests Reservation
Transaction B: locks Reservation, then requests StockItem

Prevent by accessing objects/rows in consistent order, shortening transactions, indexing predicates and avoiding overly broad locks. Capture the deadlock graph (Extended Events is a standard route) and read the resource/process edges. Do not guess from one error line.

The application can retry a deadlock victim when the entire operation is safe to replay. A stable idempotency key and no external side effects inside the transaction are important. Use bounded retry with jitter/backoff, and alert if deadlocks are sustained. Retry is not a substitute for repairing a repeatable cycle.

Junior: Should I add UPDLOCK everywhere to stop deadlocks?

>

Senior: Lock hints change concurrency semantics and can create other contention. Use them only for a proven access pattern with tests and plan evidence.

24. Index the workload, not the entity

The primary key supports exact warehouse/product access. A dashboard may query active reservations for one warehouse ordered by creation time:

SELECT
    ReservationId,
    ProductId,
    Quantity,
    CreatedAtUtc
FROM Inventory.Reservation
WHERE WarehouseId = @WarehouseId
  AND Status = 'Active'
  AND CreatedAtUtc >= @FromUtc
  AND CreatedAtUtc < @ToUtc
ORDER BY CreatedAtUtc DESC, ReservationId DESC;

A candidate filtered index:

CREATE INDEX IX_Reservation_Active_Warehouse_CreatedAt
ON Inventory.Reservation
(
    WarehouseId,
    CreatedAtUtc DESC,
    ReservationId DESC
)
INCLUDE (ProductId, Quantity)
WHERE Status = 'Active';

Why this shape?

• equality on warehouse leads;
• date range/order follows;
• reservation ID makes order deterministic;
• included columns can avoid lookups for this projection;
• the filtered predicate reduces index size for active-only workload.

But measure. If almost every row remains Active, the filter gives little reduction. If other queries need all statuses, they need a different plan/index. Every index adds write, logging, storage, backup and maintenance cost.

Do not create an index for every query independently. Review overlap, key order, included width and actual usage. A foreign key does not automatically receive a supporting index; add one when delete/update/join workload needs it.

25. Cardinality estimates drive plan choices

The optimiser estimates rows at each operator. Estimates influence join algorithms, memory grants, access methods and parallelism.

Large estimated-versus-actual differences can come from stale/inadequate statistics, correlated columns, skew, parameter sensitivity, complex predicates, table variables/temp structures or implicit conversions.

Read an actual plan alongside runtime evidence:

• actual and estimated row counts;
• logical reads and CPU/elapsed time;
• scans/seeks and residual predicates;
• key lookups repeated at scale;
• spills and memory grant feedback;
• warnings/implicit conversions;
• join types and input sizes;
• parallelism and skew;
• waits/blocking outside the plan.

An operator's displayed cost percentage is estimated relative cost, not measured wall-clock attribution. Start with the query's user impact and actual work.

Use Query Store to retain query/plan/runtime history where configured. It helps find regressions and compare plans, but forced plans are an operational mitigation with lifecycle, not a permanent substitute for understanding.

26. Parameter sensitivity and plan stability

One warehouse has ten reservations; another has ten million. A cached plan compiled for one parameter distribution may be poor for the other.

Do not solve every slow procedure with local variables, OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN. Each changes optimisation/caching and CPU/plan quality.

Diagnose:

1. Compare runtime by parameter distribution.
2. Inspect Query Store plan variation/history.
3. Verify statistics and data skew.
4. Check whether one query is representing genuinely different workloads.
5. Consider query branching, recompilation on a selective statement, platform parameter-sensitive optimisation features, or schema/index changes according to supported SQL Server version.

Dynamic SQL via sp_executesql can create specialised optional-filter queries while remaining parameterised:

DECLARE @sql nvarchar(max) = N'
SELECT ReservationId, ProductId, Quantity, CreatedAtUtc
FROM Inventory.Reservation
WHERE WarehouseId = @WarehouseId';

IF @Status IS NOT NULL
    SET @sql += N' AND Status = @Status';

SET @sql += N' ORDER BY CreatedAtUtc DESC;';

EXEC sys.sp_executesql
    @sql,
    N'@WarehouseId uniqueidentifier, @Status varchar(20)',
    @WarehouseId,
    @Status;

Only fixed SQL fragments are concatenated; values stay parameters. Arbitrary sort columns require a strict allow-list.

27. Pagination must be deterministic

Offset pagination is simple but can become slower for deep pages and unstable when rows are inserted between requests.

Keyset/seek pagination continues after the last sort key:

SELECT TOP (@PageSize)
    ReservationId,
    ProductId,
    Quantity,
    CreatedAtUtc
FROM Inventory.Reservation
WHERE WarehouseId = @WarehouseId
  AND Status = 'Active'
  AND
  (
      CreatedAtUtc < @LastCreatedAtUtc
      OR
      (CreatedAtUtc = @LastCreatedAtUtc AND ReservationId < @LastReservationId)
  )
ORDER BY CreatedAtUtc DESC, ReservationId DESC;

The order/index align, and the unique tie-breaker prevents duplicate/omitted rows caused by equal timestamps. The continuation token should be validated/protected if exposed to untrusted clients.

Offset remains appropriate for small bounded sets and direct page-number navigation. Choose from user needs and measured scale. Neither method creates a transactionally frozen list; define behaviour when data changes.

28. Data types encode business meaning

Use decimal(p,s) for fixed-precision amounts and agree rounding. money has fixed SQL Server semantics/scale; do not choose it merely for its name. Binary floating-point (float, C# double) is unsuitable for exact financial equality.

Use datetime2 for instants when storing normalised UTC, or datetimeoffset when the source offset must be preserved. A business local date may be date. Do not store dates in strings.

Choose Unicode (nvarchar) when required. Size columns from domain/contract and security constraints, not nvarchar(max) by default. Maximum types can affect row storage, memory grants and client behaviour.

Use integers appropriate to range. An identity key supplies generation, not business identity or security. GUID keys enable distributed creation but random clustering can fragment/expand indexes; choose clustered key and generation strategy from workload.

Collation controls comparison/sort/case/accent rules. Search semantics are business behaviour. Implicit conversion between parameter/column types can prevent efficient seeks, so align application parameter types exactly.

29. Constraints are concurrent correctness

Application validation gives friendly errors, but only the database sees all writers at commit. Use:

• primary/unique constraints for identity;
• foreign keys for relationships;
• check constraints for row-local allowed states/ranges;
• NOT NULL for required stored facts;
• defaults only when the database truly owns a valid default.

Do not use a default empty GUID/date to avoid nullability; it creates invalid-looking valid storage. Do not disable constraints for loading and forget to revalidate trusted state.

Some cross-row/business rules need transaction/application logic because a check constraint cannot query arbitrary other rows safely. A unique filtered index can enforce conditional uniqueness, such as one Active reservation per request concept, depending on schema.

Name constraints so errors and migrations are diagnosable. Translate expected violations to stable application outcomes without parsing human error messages where a reliable error/constraint identity is available.

30. Temporal history, audit and change tracking are different

System-versioned temporal tables can retain row history for supported table changes. They are useful for point-in-time investigation and recovery scenarios, but they are not a full business audit: they do not automatically explain actor, reason or request context.

An audit event should record the business action, actor, target, time, safe reason/category and result under retention/access controls. Change Data Capture/change tracking serve integration/synchronisation purposes with their own semantics and retention.

Do not enable every feature without calculating storage, query and operational cost. Sensitive values copied into history remain sensitive and complicate deletion/retention.

For reservations, domain audit may record Created, Released and Fulfilled transitions with request/correlation identity. Database history can help reconstruct changed columns. They complement rather than replace each other.

31. Secure SQL Server from the application boundary inward

Use parameterised commands/EF-generated parameters. Parameters protect values; dynamic identifiers/sort directions require allow-listing.

Give the application login only required permissions. Separate migration/admin credentials from runtime. Prefer schema/module grants or signed procedures where they fit governance. Rotate credentials or use managed/workload identity where available.

Row-level security can add tenant filtering, but it must be correctly keyed to trusted session context and tested for every access path. It complements—not replaces—application resource authorisation. Connection pooling requires session context to be set/reset safely.

Protect backups, replicas, exports, Query Store/monitoring and non-production copies. Dynamic data masking is presentation obfuscation, not access control or encryption.

Encryption at rest and in transit protect different threats. Highly sensitive columns may need additional encryption/key design, which affects querying/indexing and operations.

Audit privileged access. Avoid logging SQL parameter values containing personal or secret data. Plans and query capture can expose literals depending on how SQL is issued/configured.

32. Migrations are production workloads

Adding a nullable column is often cheap; adding a populated non-null column or rebuilding a huge index can be expensive. Test with production-shaped size and load.

Use expand-and-contract:

1. Add compatible schema.
2. Deploy code supporting old/new form.
3. Backfill in small restartable batches.
4. Monitor locks, log growth and replication.
5. verify completeness.
6. Switch reads/writes.
7. enforce final constraint.
8. remove old schema later.

Do not wrap an hours-long backfill in one transaction. Use a stable key range and checkpoint:

WHILE 1 = 1
BEGIN
    UPDATE TOP (5000) Inventory.Reservation
    SET NewStatusCode = CASE Status
        WHEN 'Active' THEN 1
        WHEN 'Released' THEN 2
        WHEN 'Fulfilled' THEN 3
    END
    WHERE NewStatusCode IS NULL;

    IF @@ROWCOUNT = 0 BREAK;

    WAITFOR DELAY '00:00:00.100';
END;

This is illustrative; add deterministic batching/checkpoints and operational controls for the real table. WAITFOR pacing is not a universal solution.

Make migrations forward-compatible and reviewed. Application rollback usually keeps the expanded schema; destructive rollback may be slower/riskier than forward repair.

33. Backups are only useful after restore testing

Define Recovery Point Objective (acceptable data loss) and Recovery Time Objective (acceptable restoration time). Choose full, differential and transaction log backup strategy accordingly, considering recovery model and business requirements.

Test restoration to an isolated environment. Verify:

• backup chain and checksums/validation appropriate to policy;
• actual restore duration;
• point-in-time recovery;
• database consistency checks;
• logins/users, keys/certificates and permissions;
• application smoke and reconciliation;
• downstream/outbox/idempotency state;
• documented failover/failback.

High availability is not backup. Replication can replicate accidental deletion or corruption. Backup copies need retention, encryption, access controls and protection from the same failure/ransomware domain.

Run exercises. A runbook never executed is a hypothesis.

34. Observe database health through workload signals

Collect and correlate:

• query duration, CPU and logical reads;
• waits by category over time;
• blocking/deadlock events;
• batch/request rate;
• connection pool/use;
• transaction log growth/reuse;
• tempdb/version-store pressure;
• memory grant/spill signals;
• storage latency and capacity;
• index/statistics maintenance outcomes;
• backup/restore and integrity-check results.

Wait statistics are clues, not direct prescriptions. A high wait total can reflect normal workload volume. Compare baselines, recent change and user impact.

Application traces should include safe operation/query identity and database dependency time. Do not make every SQL text or customer ID a metric label.

Set alerts around sustained service objectives: reservation latency/error/conflict, blocking duration, outbox age, log/capacity risk and failed backups. One expensive ad hoc query may need investigation without paging everyone.

35. Test database behaviour, not an imitation

Unit tests can validate query-building logic and domain decisions. Integration tests against SQL Server prove constraints, collation, translation, concurrency, transaction and isolation behaviour.

For reservation, run two connections/tasks against one stock row with a barrier. Both request the last quantity. Assert one reservation commits, one receives insufficient/conflict and ReservedQuantity <= OnHandQuantity.

Also test:

• duplicate request ID concurrently;
• rollback after reservation update before insert;
• deadlock victim retry with same idempotency identity;
• timeout/cancellation and uncertain outcome reconciliation;
• query pagination with equal timestamps;
• cross-warehouse/tenant access;
• migration against representative size;
• restore plus application reconciliation.

SQLite/in-memory substitutes do not reproduce SQL Server locking, rowversion, collations, filtered indexes or plan behaviour. Use them only for claims they can support.

Performance tests need representative volume and skew. Retain query, plan, parameters (safely), reads, CPU, elapsed and server configuration enough to compare.

36. Diagnose four production incidents

Incident one: reservation API is slow, SQL CPU is low

Find blocking and open transactions. A supplier HTTP call occurs after the update but before commit. Move the remote call outside the transaction or redesign as asynchronous workflow. Keep reservation transaction atomic/short.

Incident two: one warehouse is slow

Query Store shows one cached plan used for tiny and huge warehouses. Inspect skew/statistics and plan history. Choose a version-appropriate parameter-sensitivity/query strategy based on evidence, not blanket recompilation.

Incident three: dashboard totals change during refresh

Multiple statements read different committed moments, or NOLOCK returns inconsistent data. Define snapshot requirement. Use a warehouse/read replica/snapshot isolation approach as appropriate and label data watermark.

Incident four: duplicate reservation after client timeout

The app checks request ID then inserts without a unique constraint/transaction. Two requests race. Add database uniqueness, atomic handling and request fingerprint. On an uncertain response, query by request ID before retrying the business action.

Each incident review should update code, schema/query, tests, telemetry and runbook. Do not stop at “cleared the blocking session.”

37. Normalisation through the reservation model

Suppose the first design stores this row:

ReservationId, ProductId, ProductName, SupplierName, WarehouseId,
WarehouseAddress, Quantity, SiteManagerEmail, Status

Product name repeats for every reservation. Warehouse address repeats. A supplier rename requires many updates. A typo can create conflicting realities. Normalisation separates facts according to their dependencies:

Product(ProductId, ProductName, SupplierId, ...)
Supplier(SupplierId, SupplierName, ...)
Warehouse(WarehouseId, AddressId, ...)
StockItem(WarehouseId, ProductId, OnHandQuantity, ReservedQuantity, ...)
Reservation(ReservationId, WarehouseId, ProductId, Quantity, Status, ...)

First normal form requires atomic values in the relational design rather than comma-separated product lists. Second/third normal-form reasoning removes attributes dependent on only part of a composite key or on another non-key attribute.

Do not normalise from slogans. If a reservation must preserve the product description shown at order time, storing an immutable snapshot on the reservation may be correct historical data rather than accidental duplication. Name it ProductDescriptionAtReservation, document its ownership and do not update it when the catalogue changes.

Likewise, reporting may deliberately denormalise into facts/dimensions. OLTP normalisation protects transactional updates; analytical denormalisation supports stable read grain. Never let a reporting convenience become a second uncontrolled source of stock truth.

Optional relationships and nullability

NULL should mean an understood absence/unknown/not-applicable state, not several meanings hidden in one column. If a reservation may have a release reason only when Released, a nullable foreign key can be valid with a cross-column check:

ALTER TABLE Inventory.Reservation
ADD ReleaseReasonId int NULL;

ALTER TABLE Inventory.Reservation
ADD CONSTRAINT CK_Reservation_ReleaseReason
CHECK
(
    (Status = 'Released' AND ReleaseReasonId IS NOT NULL)
    OR
    (Status <> 'Released' AND ReleaseReasonId IS NULL)
);

The workflow still controls transition; the constraint prevents impossible stored combinations.

38. EF Core and SQL Server must agree

An ORM does not remove database behaviour. Review generated SQL, parameter types, transaction scope and query shape.

For a stock summary:

var page = await db.StockItems
    .AsNoTracking()
    .Where(x => x.WarehouseId == warehouseId)
    .Where(x => x.OnHandQuantity > x.ReservedQuantity)
    .OrderBy(x => x.Product.Name)
    .ThenBy(x => x.ProductId)
    .Select(x => new StockListItem(
        x.ProductId,
        x.Product.Name,
        x.OnHandQuantity - x.ReservedQuantity,
        x.RowVersion))
    .Take(pageSize)
    .ToListAsync(cancellationToken);

Check that arithmetic and joins translate, ordering is deterministic and the query does not retrieve an entire graph. AsNoTracking suits this read projection. The write path loads/tracks the aggregate or issues an intentional atomic SQL update.

EF optimistic concurrency using rowversion produces an update predicate containing the original token. Zero affected rows become DbUpdateConcurrencyException. Translate to a business conflict and reload; do not expose the binary token's internal meaning.

Raw SQL for atomic updates

Some invariants are clearer in one SQL statement than read-modify-save tracking:

var affected = await db.Database.ExecuteSqlInterpolatedAsync($"""
    UPDATE Inventory.StockItem
    SET ReservedQuantity = ReservedQuantity + {quantity}
    WHERE WarehouseId = {warehouseId}
      AND ProductId = {productId}
      AND OnHandQuantity - ReservedQuantity >= {quantity};
    """, cancellationToken);

Interpolated EF APIs parameterise embedded values; verify the exact method/overload. Never replace with concatenated raw SQL. Wrap the update and reservation insert in the same transaction. If rows affected is zero, query deliberately to distinguish not found from insufficient only when the contract needs it.

Avoid multiple active operations

DbContext is not thread-safe. Do not execute two queries concurrently on one context with Task.WhenAll. Use separate contexts/connections only when parallel database work is justified; more concurrent queries can increase load and contention rather than reduce end-to-end latency.

Connection pools are finite

Open connections late and dispose promptly. ADO.NET pooling makes physical reuse efficient, but a long reader/transaction holds capacity. A timeout waiting for a pooled connection may indicate leak, excessive concurrency or slow database work—not a need to increase the maximum immediately.

39. ADO.NET command safety and streaming

When using ADO.NET directly, set parameter type/size explicitly:

await using var command = connection.CreateCommand();
command.CommandText = """
    SELECT ReservationId, ProductId, Quantity, CreatedAtUtc
    FROM Inventory.Reservation
    WHERE WarehouseId = @WarehouseId
      AND Status = @Status
      AND CreatedAtUtc >= @FromUtc
      AND CreatedAtUtc < @ToUtc
    ORDER BY CreatedAtUtc, ReservationId;
    """;

command.Parameters.Add("@WarehouseId", SqlDbType.UniqueIdentifier).Value = warehouseId;
command.Parameters.Add("@Status", SqlDbType.VarChar, 20).Value = "Active";
command.Parameters.Add("@FromUtc", SqlDbType.DateTime2).Value = fromUtc;
command.Parameters.Add("@ToUtc", SqlDbType.DateTime2).Value = toUtc;

await using var reader = await command.ExecuteReaderAsync(cancellationToken);

AddWithValue can infer an unexpected size/type (for example Unicode string) and cause implicit conversion or plan variation. Explicit parameters align with the column and contract.

Streaming rows avoids one huge list allocation, but holds the connection/reader. Apply bounds, cancellation and backpressure. For a million-row export, use an asynchronous export job with dedicated resource controls rather than a long interactive request.

Cancellation asks the provider/server to stop; it may arrive after work/commit. For commands, reconcile by request identity. Set command timeouts from journey budgets and operational reality, not infinity or one global arbitrary value.

40. Stored procedures as supported contracts

Stored procedures can encapsulate atomic operations, restrict permissions and provide stable database-side contracts. They are not automatically faster, safer or cleaner.

A reservation procedure should return explicit outcome codes/data, use parameter types matching columns, keep transactions short and avoid calling external systems.

CREATE OR ALTER PROCEDURE Inventory.TryCreateReservation
    @ReservationId uniqueidentifier,
    @RequestId uniqueidentifier,
    @WarehouseId uniqueidentifier,
    @ProductId uniqueidentifier,
    @Quantity int,
    @CreatedAtUtc datetime2(3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @Quantity <= 0
        THROW 50002, 'Quantity must be positive.', 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF EXISTS (
            SELECT 1
            FROM Inventory.Reservation
            WHERE RequestId = @RequestId)
        BEGIN
            SELECT 'Duplicate' AS Outcome, ReservationId, Status
            FROM Inventory.Reservation
            WHERE RequestId = @RequestId;

            COMMIT TRANSACTION;
            RETURN;
        END;

        UPDATE Inventory.StockItem
        SET ReservedQuantity = ReservedQuantity + @Quantity
        WHERE WarehouseId = @WarehouseId
          AND ProductId = @ProductId
          AND OnHandQuantity - ReservedQuantity >= @Quantity;

        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRANSACTION;
            SELECT 'Unavailable' AS Outcome;
            RETURN;
        END;

        INSERT Inventory.Reservation
        (
            ReservationId, RequestId, WarehouseId, ProductId,
            Quantity, CreatedAtUtc, Status
        )
        VALUES
        (
            @ReservationId, @RequestId, @WarehouseId, @ProductId,
            @Quantity, @CreatedAtUtc, 'Active'
        );

        COMMIT TRANSACTION;
        SELECT 'Created' AS Outcome, @ReservationId AS ReservationId;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

This procedure still has a race between the existence check and concurrent insert; the unique constraint is authoritative, and duplicate-key handling must return/reconcile the existing matching request. The example highlights why a happy-path procedure is not finished until concurrency is tested.

Version stored procedure contracts with application deployment. Keep scripts in source control, integration-test result sets and grant execute rather than broad table access where that model fits.

Avoid enormous procedures containing every business workflow, dynamic filters and UI shaping. They become difficult to test/version/deploy. The database should own atomic data work and constraints; application/domain layers own broader workflow.

41. Temporary tables, table variables and staging choices

Temporary structures are tools for intermediate relational work.

Local temporary tables (#Items) support indexes/statistics and can be useful when breaking a complex plan, processing a batch or reusing an intermediate set. They use tempdb and have creation/load cost.

Table variables have different estimation/compilation behaviour depending on SQL Server version/features and are not simply “memory-only.” They can work for small bounded sets; measure with actual data.

Table-valued parameters efficiently pass sets from an application, but SQL Server has limited statistics knowledge for them. For large/skewed inputs, copying to a temp table with an appropriate index can produce a better plan.

CREATE TABLE #RequestedProducts
(
    ProductId uniqueidentifier NOT NULL PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity > 0)
);

INSERT #RequestedProducts (ProductId, Quantity)
SELECT ProductId, Quantity
FROM @RequestedProducts;

Bulk reservation across products introduces ordering/deadlock and all-or-nothing/partial business choices. Sort/update stock in a stable key order and define result per product. Do not use a cursor by default, but do not force a single clever statement if it obscures correctness.

42. Maintenance follows evidence

Indexes become fragmented and statistics become stale/change. Maintenance should match workload, database size, storage and SQL Server capabilities—not a nightly script rebuilding everything.

Rebuilding indexes consumes CPU, I/O, log, space and can affect availability/replication. Reorganising is lighter but different. Some fragmentation is harmless, especially on small indexes or workloads dominated by memory/sequential access.

Statistics updates help cardinality estimates; automatic statistics are valuable but may need targeted supplementation for skew/rapid change. Sampling/fullscan has cost. Monitor plan quality rather than chasing a percentage dashboard.

Integrity checks (DBCC CHECKDB strategies) are essential but resource-intensive. Schedule/restore-test according to risk and capacity; run checks against restored backups where appropriate, while understanding what each check covers.

Capacity-plan data, log, tempdb, backup and version-store growth. Autogrowth is a safety mechanism, not primary capacity management. Pre-size files sensibly and alert before disks fill.

43. Partitioning and compression are physical design

Partitioning can improve manageability and partition elimination when queries include the partition key. It does not automatically speed every query, and a poorly aligned index design can complicate uniqueness and maintenance.

Reservations might partition by creation month for retention/archive, but active reservations can span months. A status-centric query may touch many partitions. Model the workload before choosing.

Compression can reduce I/O/storage at CPU cost. Evaluate row/page/columnstore options based on OLTP versus analytical usage and supported edition/version. A nonclustered columnstore index can help operational analytics in some workloads but adds write/maintenance considerations.

Archive old fulfilled/released reservations only if application, audit and reporting contracts support it. Moving rows changes plans, foreign keys and restoration. Retention is a governance decision, not just table size management.

44. Replicas and availability do not erase consistency choices

Readable secondaries can offload reports, but replication is asynchronous in many configurations and data may lag. Display/measure the data watermark. Never route a read-after-write confirmation to a replica that may not contain the reservation yet unless the UI handles that delay.

Availability groups/failover clustering improve availability but add quorum, listener, backup, job and failover operational requirements. Test application connection retry and transaction uncertainty during failover. A command may commit on the primary while the client receives a connection error; idempotency/reconciliation remains necessary.

Failover is not disaster recovery by itself and not backup. Test planned/unplanned failover and failback. Confirm SQL Agent jobs, logins, credentials and maintenance follow the active role.

45. A worked SQL code review

The pull request contains:

SELECT *
FROM Inventory.Reservation r
JOIN Inventory.StockItem s ON s.ProductId = r.ProductId
WHERE CONVERT(date, r.CreatedAtUtc) = @Date
  AND r.WarehouseId = @WarehouseId
ORDER BY r.CreatedAtUtc DESC;

Review systematically.

Correctness

The join omits WarehouseId, so a product stocked in many warehouses multiplies/mismatches rows. The intended grain must be stated. SELECT * creates duplicate column names and contract expansion.

Time semantics

@Date lacks timezone meaning. Convert the user's/business local date to explicit UTC start/exclusive end in trusted application code or a carefully defined SQL conversion. Filtering CONVERT(date, column) is non-SARGable for a normal timestamp index.

Contract

Select the required DTO columns with aliases. Add deterministic ReservationId tie-breaker and pagination. Decide whether status, released rows and current stock are actually needed.

Performance

After correction, inspect actual plan/reads with representative warehouse/date skew. The earlier active filtered index may not serve all-status date queries. Design/retain an index only if workload warrants it.

Security

Parameterise values with exact SQL types. Warehouse authorisation must be enforced above or through a secure database design; possessing an ID is not permission. Restrict runtime select to approved objects/views.

Corrected shape:

SELECT TOP (@PageSize)
    r.ReservationId,
    r.ProductId,
    r.Quantity,
    r.Status,
    r.CreatedAtUtc,
    s.OnHandQuantity - s.ReservedQuantity AS CurrentAvailableQuantity
FROM Inventory.Reservation AS r
JOIN Inventory.StockItem AS s
  ON s.WarehouseId = r.WarehouseId
 AND s.ProductId = r.ProductId
WHERE r.WarehouseId = @WarehouseId
  AND r.CreatedAtUtc >= @StartUtc
  AND r.CreatedAtUtc < @EndUtc
ORDER BY r.CreatedAtUtc DESC, r.ReservationId DESC;

Even this query mixes historical reservation facts with current availability; label the latter clearly or separate it, because it can change between page loads.

The review comment should say consequence and evidence: “The current join omits warehouse and can duplicate/cross-match stock rows. Please join on the full key, replace date conversion with explicit UTC bounds, project the response columns, add deterministic pagination, and attach actual-plan/read evidence for representative warehouse sizes.”

46. Mentoring exercises

Exercise one: concurrency laboratory

Implement read-check-update and reproduce overselling with two sessions. Replace it with conditional atomic update plus reservation insert. Repeat 1,000 competing trials and assert the invariant.

Exercise two: read an actual plan

Load skewed warehouses, run the active-reservation query, record estimates/actual rows/reads/time, then add the candidate filtered index. Measure both read benefit and reservation write overhead.

Exercise three: deadlock game

Create two procedures that lock StockItem and Reservation in opposite order. Capture the deadlock graph, identify the cycle, standardise access order and verify it disappears under load.

Exercise four: migration rehearsal

Add a non-null status code to a production-sized copy using expand/backfill/contract. Record blocking, log growth, duration and rollback/forward recovery. Adjust batch size from evidence.

Exercise five: restore

Restore to a point before/after a known reservation, run integrity checks, reconcile stock/reservations/outbox and measure RTO. Improve the runbook wherever manual knowledge was required.

47. Live incident playbook: reservations exceed the latency objective

At 10:05, the reservation endpoint's p95 rises from 180 milliseconds to twelve seconds. Error rate remains low, SQL Server CPU is 25%, and adding an application instance does not help.

Junior: CPU is low, so is SQL Server healthy?

>

Senior: Low CPU only says the processors are not the current limit. Requests may be waiting on locks, storage, memory grants, log flush, connections or a downstream call inside a transaction.

First five minutes: protect evidence and impact

Confirm the user journey and affected scope: every warehouse or one, reads or writes, all application versions or a recent deployment. Capture the time window, request IDs and database/client error categories.

Do not restart SQL Server, clear the plan cache or kill sessions blindly. Those actions destroy evidence and can roll back large transactions, causing more blocking. If business impact demands mitigation, route/pause the affected command according to a rehearsed runbook while preserving idempotent requests.

Check:

• availability and connection establishment;
• current blocking chain/head blocker;
• active requests, waits and transaction age;
• Query Store changes/regressed plans;
• storage/log/tempdb/version-store pressure;
• application pool saturation/timeouts;
• deployment/configuration/migration timeline.

Use approved diagnostic tools/views/scripts with least privilege. Avoid copying sensitive parameter values into incident chat.

Hypothesis one: open transaction around supplier call

The head blocker is an application session whose transaction started eight minutes ago. Its current request appears idle because C# is waiting on an HTTP supplier call after updating StockItem.

Verify through trace/span timeline, transaction/session context and blocking graph. The durable repair is to move the remote operation before/after the local transaction or model a saga/outbox workflow. The immediate mitigation may cancel/kill the blocker only after assessing rollback work and business outcome. Reconcile request IDs afterward; some reservations may have committed or rolled back ambiguously from the caller's view.

Hypothesis two: plan regression

No long blocker exists. One warehouse query now performs millions of reads. Query Store shows a plan change after statistics/data distribution changed.

Compare plans, parameters, estimates/actual rows and runtime. A forced known-good plan can be a controlled temporary mitigation if validated, but record ownership and expiry. Repair statistics, indexing, query shape or parameter-sensitivity design. Test across small and huge warehouses so the fix does not move the incident.

Hypothesis three: connection pool starvation

Application requests wait before SQL execution, while database activity appears modest. Inspect pool/connection timings and disposal paths. A new export streams slowly while holding many connections. Bound export concurrency, move it to background jobs and ensure readers/connections dispose. Increasing pool size without database capacity and query repair can amplify load.

Verify recovery

Do not declare recovery when one query returns quickly. Confirm:

• p50/p95/p99 and error rate return to baseline;
• blocking chain clears and transaction age normalises;
• no growing rollback, queue or outbox backlog remains;
• stock/reservation invariants reconcile;
• uncertain request IDs return one authoritative result;
• mitigation does not create secondary pressure;
• telemetry retains enough evidence for review.

Prevent recurrence

Add a test or guard at the failure boundary: transaction duration telemetry, command deadline, architecture rule against network work inside transactions, representative plan regression test, bounded export workers or connection-leak test. Update runbook and alert on user-impacting symptoms plus actionable transaction/queue age.

The lesson is method: start from the affected operation, preserve evidence, classify waiting versus running, test hypotheses with correlated signals, mitigate reversibly and reconcile data before closing.

48. Design a safe data-correction script

Production corrections deserve the same rigour as application releases. Suppose 417 reservations imported with Status = 'Active' although fulfilment events prove they are Fulfilled.

First produce an immutable target set from an authoritative rule, peer-reviewed with the business owner:

SELECT
    r.ReservationId,
    r.Status AS CurrentStatus,
    f.FulfilledAtUtc
INTO #CorrectionTargets
FROM Inventory.Reservation AS r
JOIN Inventory.Fulfilment AS f
  ON f.ReservationId = r.ReservationId
WHERE r.Status = 'Active'
  AND f.FulfilledAtUtc < @CutoffUtc;

SELECT COUNT(*) AS TargetCount FROM #CorrectionTargets;

Validate counts, samples, tenant/warehouse scope and absence of conflicting later events. Do not use an editable spreadsheet list as sole authority.

Apply in bounded batches with expected-current-state predicate so concurrent legitimate changes are not overwritten:

UPDATE TOP (500) r
SET Status = 'Fulfilled'
OUTPUT
    inserted.ReservationId,
    deleted.Status,
    inserted.Status
INTO Audit.ReservationCorrection
FROM Inventory.Reservation AS r
JOIN #CorrectionTargets AS t
  ON t.ReservationId = r.ReservationId
WHERE r.Status = 'Active';

The real audit table also records correction ID, approver/operator, reason and time under policy. Re-run selection between batches or lock/freeze the target according to concurrency needs. Reconcile target, changed, skipped and remaining counts.

Assess side effects: should stock counters change, integration events publish, read models rebuild or caches invalidate? Direct SQL bypasses domain/application behaviour. Sometimes a supported repair command/tool is safer than an update script. If SQL is required, explicitly reproduce every necessary invariant and downstream action.

Test against a restored copy, estimate log/lock impact, prepare stop/forward-repair steps and monitor. “Wrap everything in a transaction and roll back if worried” can create a huge long-running transaction and is not automatically safe.

After correction, query constraints/invariants, reconcile stock totals, rebuild affected projections and notify consumers of any reporting change. Store the reviewed script and evidence, then build a supported prevention/detection path so the same manual operation is unnecessary.

49. Production readiness checklist for reservation

• Schema keys and constraints express stored invariants.
• Conditional update prevents overselling atomically.
• Reservation and stock update share one short transaction.
• Request ID uniqueness makes retries idempotent.
• Duplicate key returns the original matching result safely.
• No remote call occurs while SQL locks are held.
• Isolation choice and snapshot options are documented/tested.
• Deadlocks are captured and bounded retries are safe.
• Query indexes match measured predicates/order/projection.
• Pagination has a deterministic tie-breaker.
• Runtime credentials are least privilege.
• Migration supports mixed application versions.
• Backup/point-in-time restore is rehearsed.
• Telemetry correlates request, SQL and outcome safely.
• Concurrency/integration tests run on SQL Server.

50. Continue the learning path

Use SQL and Data Analytics for Senior Developers for warehouse/metric depth, EF Core Best Practices for ORM access, Clean Architecture with .NET for persistence boundaries, and How to Investigate Slow Angular, ASP.NET Core and SQL Server Applications for cross-stack diagnosis. The C# Async/Concurrency and HTTP guides reinforce cancellation, retry and idempotency.

The database is not merely an implementation detail when it enforces concurrent truth. Application and SQL design must agree on the invariant and failure contract.

Definition of done for the reservation workload

The reservation feature is ready when concurrent sessions cannot make reserved stock exceed on-hand stock, and the same logical request cannot create two reservations. A missing row, insufficient quantity, duplicate matching request, reused key with different content, concurrency conflict, timeout and unexpected database failure must map to distinct, documented application outcomes.

The SQL evidence includes named constraints, exact parameter types, a short atomic transaction, actual plans and reads for representative warehouses, deterministic pagination and a tested index write/read trade-off. The operational evidence includes captured deadlock/blocking diagnostics, bounded safe retry, transaction-age and latency monitoring, database capacity baselines and a runbook that reconciles uncertain request IDs.

The release evidence includes a production-sized migration rehearsal, mixed old/new application compatibility, least-privilege runtime identity, backup/point-in-time restore practice and invariant reconciliation after restore. The test evidence uses SQL Server—not an in-memory imitation—to race reservations, duplicate request IDs, interrupt transactions and verify query semantics.

Ask a developer who did not write the feature to diagnose a deliberately blocked reservation from application trace through session, transaction, lock resource, SQL statement and final data outcome. Then ask them to restore the relevant period and prove stock plus reservations balance. If success depends on the original author remembering a hidden query or manually editing a row, the system is not yet supportable.

Finally, keep a small decision record for isolation configuration, clustered keys, idempotency identity, index choices, retention and reporting consistency. Revisit it as volume, skew and business rules change. Database design is complete only provisionally: its guarantees remain firm, while its physical strategy adapts to measured workload.

Measure the feature again after release. Track reservation correctness, latency percentiles, conflict and duplicate rates, logical reads, blocking duration, deadlocks, log growth and recovery exercises. Compare them with the baseline used to approve the design. If an index no longer serves the workload, replace it deliberately; if a constraint catches an application defect, preserve the evidence and repair the caller. The database should make invalid state difficult while remaining observable enough that the team understands the cost of every guarantee throughout the workload's complete operational lifetime in production.

---

51. Code review checklist for SQL

When reviewing SQL, I ask:

What is the purpose of the query? What is the expected row count? What is the result grain? Are columns explicit? Is filtering done in the database? Is pagination needed? Are joins correct? Can joins duplicate rows? Are data types correct? Are parameters typed correctly? Are predicates SARGable? Are indexes supporting filters, joins and order by? Is the execution plan acceptable? Are transactions short? Are errors handled? Are constraints protecting data integrity? Is this safe for production data volume?

Example bad query:

SELECT *
FROM LoanApplications
WHERE YEAR(SubmittedAt) = 2026
ORDER BY SubmittedAt DESC;

Review comments:

SELECT * returns unnecessary columns. YEAR(SubmittedAt) makes the predicate non-SARGable. No pagination. Index on SubmittedAt may not be used efficiently.

Better:

SELECT
    LoanApplicationId,
    ReferenceNumber,
    CustomerId,
    RequestedAmount,
    Status,
    SubmittedAt
FROM LoanApplications
WHERE SubmittedAt >= '2026-01-01'
  AND SubmittedAt < '2027-01-01'
ORDER BY SubmittedAt DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

Supporting index:

CREATE INDEX IX_LoanApplications_SubmittedAt
ON LoanApplications (SubmittedAt DESC)
INCLUDE (ReferenceNumber, CustomerId, RequestedAmount, Status);

52. Best practices summary

Use explicit columns. Use correct data types. Use primary keys and foreign keys. Use constraints for important rules. Normalise transactional data. Denormalise carefully for reporting. Filter early. Paginate large result sets. Design indexes for real queries. Check execution plans. Write SARGable predicates. Avoid functions on indexed columns in filters. Avoid leading wildcard searches on large tables. Avoid long transactions. Avoid loading everything into the application. Use DTO-shaped queries for screens. Use DECIMAL for money. Use DATETIME2 for dates. Use UTC for system timestamps where appropriate. Use row versioning/concurrency controls where workflows can conflict. Treat migrations as production events, not just code changes.

Final senior-level answer

If an interviewer asks you, “How strong are you with SQL?”, you should answer like this:

“I understand SQL from both application and database perspectives. I design relational tables with keys, constraints and relationships to protect data integrity. I write queries with explicit columns, correct joins, filtering, ordering and pagination. I understand that query performance depends on data volume, indexes, SARGable predicates, statistics and execution plans. I avoid common smells like SELECT *, filtering after materialisation, non-SARGable date filters, missing pagination, wrong data types, excessive joins and long transactions. I also understand the difference between OLTP transactional modelling and analytical/reporting models, including grain and aggregation risks. When reviewing SQL, I look at correctness, performance, maintainability, concurrency, production safety and whether the query matches the business question.”

That is the mindset.

SQL is not just syntax. SQL is where business truth, performance, and data quality meet.

A senior developer who understands SQL properly becomes much more valuable because they can see problems that application-only developers often miss.

Bad SQL can make a good application slow.

Good SQL can make a complex system feel effortless.

And expert SQL is not about writing clever queries. It is about writing correct, clear, safe, and scalable queries that still make sense six months later when production data has grown and the team is under pressure.