Tag: SQL Server

Dynamic SQL in SQL Server: sp_executesql and Avoiding SQL Injection
← Full Course Index

Ch. 1: Advanced T-SQL

1. Variables & Control-of-Flow
2. TRY/CATCH Error Handling
3. Dynamic SQL
Dynamic SQL in SQL Server: sp_executesql and Avoiding SQL Injection

Dynamic SQL builds a query as a string at runtime — necessary for variable table/column names, which can’t be parameterized normally (you can parameterize a WHERE value, but not “which table to query”). Done wrong, it’s also the single most common source of SQL injection, a vulnerability that has caused real, well-documented data breaches. This lesson treats that risk with the seriousness it deserves.

The Safe Pattern

DECLARE @tableName SYSNAME = 'Product'; DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT COUNT(*) AS row_count FROM ' + QUOTENAME(@tableName); EXEC sp_executesql @sql; -- Parameterized dynamic SQL — the SAFE way to inject user-supplied VALUES DECLARE @minPrice DECIMAL(10,2) = 20.00; SET @sql = N'SELECT name, price FROM dbo.Product WHERE price >= @MinPrice'; EXEC sp_executesql @sql, N'@MinPrice DECIMAL(10,2)', @MinPrice = @minPrice;

How Injection Actually Happens

-- NEVER do this — direct string concatenation of user input is a SQL injection hole DECLARE @userInput NVARCHAR(100) = ''';DROP TABLE dbo.Product;--'; DECLARE @unsafeSql NVARCHAR(MAX) = N'SELECT * FROM dbo.Product WHERE name = ''' + @userInput + ''''; -- @unsafeSql is now: SELECT * FROM dbo.Product WHERE name = '';DROP TABLE dbo.Product;--' -- If executed, this drops the table.

Walk through exactly why this works: the attacker’s input closes the intended string literal early with a stray ', appends a semicolon to start a brand-new statement, adds their own malicious SQL (DROP TABLE...), and then -- comments out whatever was supposed to follow in the original query, so it doesn’t cause a syntax error. Every part of that trick relies on user input being treated as executable code text rather than as inert data — which is exactly what sp_executesql parameters prevent, by keeping the query’s shape fixed and passing values through a separate channel the engine can never reinterpret as code.

The Rule, Visualized

Table/column/schema names must be concatenated (there’s no parameter placeholder for “which table”), sanitized with QUOTENAME(), which wraps the identifier in brackets and escapes any embedded bracket characters — this is what makes it safe, not just convention. But actual data values must always go through sp_executesql parameters, never string concatenation.

QUOTENAME Isn’t Optional Even for “Trusted” Input

-- Without QUOTENAME, a table name containing a bracket or reserved word breaks or worse: DECLARE @table SYSNAME = 'Order'; -- a reserved keyword SET @sql = N'SELECT * FROM ' + @table; -- syntax error, or worse if attacker-controlled -- With QUOTENAME, it's safely wrapped regardless of content: SET @sql = N'SELECT * FROM ' + QUOTENAME(@table); -- becomes: SELECT * FROM [Order]

Common mistake: Assuming dynamic SQL is only risky when input comes directly from a web form. Any value that ultimately traces back to something a user can influence — a config setting they can edit, a CSV they upload that gets read into a variable, a table/column name selected from a dropdown — needs the same treatment. “Internal tool, so it’s fine” is exactly the reasoning that leads to real incidents.

Why Use Dynamic SQL At All?

Given the risk, it’s worth being clear about when dynamic SQL is genuinely the right tool: building a search query with an unpredictable number of optional filters, generating administrative scripts that operate across a variable list of tables, or building reports where the pivoted columns aren’t known until runtime. For everything else — the vast majority of real T-SQL — a normal parameterized query or stored procedure with fixed parameters is simpler, safer, and lets the query optimizer cache and reuse execution plans more effectively.

Practice tip: Take the unsafe concatenation example above, actually build the malicious string yourself in a scratch variable, and PRINT it (without executing it) to see exactly what SQL an attacker’s input would produce. Seeing the constructed statement in full is far more convincing than reading about the risk abstractly.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

First Name

Last Name

Please verify that you are human.

Email address:

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.
July 18, 2026
T-SQL Variables, Batches, and Control-of-Flow: Beyond Basic Queries
← Full Course Index

Ch. 1: Advanced T-SQL

1. Variables & Control-of-Flow
2. TRY/CATCH Error Handling
3. Dynamic SQL
T-SQL Variables, Batches, and Control-of-Flow: Beyond Basic Queries

This is where SQL Server stops being just a query language and starts behaving like a real programming environment. Everything in the SQL Server Fundamentals course was declarative — describe the result you want, let the engine figure out how. Variables, IF/ELSE, and loops introduce procedural logic on top of that, which is exactly what stored procedures, functions, and triggers (the rest of this course) are built from.

Declaring and Using Variables

DECLARE @threshold DECIMAL(10,2) = 50.00; DECLARE @productCount INT; SELECT @productCount = COUNT(*) FROM dbo.Product WHERE price > @threshold; PRINT 'Products above threshold: ' + CAST(@productCount AS VARCHAR(10)); IF @productCount > 0 PRINT 'At least one premium product exists.'; ELSE PRINT 'No premium products found.';

Notice SELECT @productCount = COUNT(*) ... — this is SQL Server’s idiom for assigning a query’s result directly into a variable, distinct from a normal SELECT that returns a result set. If the query returns zero rows, the variable is set to NULL, not left unchanged; if it returns multiple rows, the variable silently ends up holding the last row’s value, which is rarely what you want and worth watching for.

The Batch-Scoping Trap

A batch is a group of statements sent to SQL Server together, separated by GO (a client-side signal understood by SSMS/sqlcmd, not real T-SQL — the server itself never sees it). Variables declared in one batch don’t exist in the next, because each batch is compiled and executed as an entirely separate unit. This trips people up constantly when copy-pasting scripts with stray GO statements in the middle, or when a script generator inserts a GO you didn’t expect.

-- Also worth knowing: DDL statements sometimes REQUIRE their own batch CREATE VIEW dbo.vw_Test AS SELECT 1 AS col; -- must be the first/only statement in its batch GO

WHILE Loops: A Last Resort, Not a First Instinct

DECLARE @i INT = 1; WHILE @i <= 3 BEGIN PRINT 'Iteration ' + CAST(@i AS VARCHAR(10)); SET @i += 1; END;

WHILE loops process one row at a time and are almost always slower than an equivalent set-based UPDATE/SELECT — this is the single biggest mental adjustment for developers coming from procedural languages, where a for-loop is the default tool. In SQL Server, reach for a loop only when the logic genuinely can't be expressed as a single set operation (batch-processing a huge table in chunks to avoid one giant transaction is a legitimate use case; iterating row by row to do what an UPDATE could do in one statement is not).

-- The set-based equivalent of "loop through rows and apply a 10% discount" — -- no loop needed, and dramatically faster on any real table size: UPDATE dbo.Product SET price = price * 0.9 WHERE category = 'Clearance';

Common mistake: Writing a WHILE loop that fetches one row, processes it, and moves to the next — essentially reimplementing a cursor by hand. If you find yourself doing this, stop and ask whether the whole operation can be expressed as one UPDATE, INSERT...SELECT, or MERGE statement instead. It almost always can.

BEGIN/END Blocks and Nesting

DECLARE @orderTotal DECIMAL(10,2) = 150.00; IF @orderTotal > 100 BEGIN IF @orderTotal > 500 PRINT 'Free shipping + gift wrap'; ELSE PRINT 'Free shipping'; END ELSE PRINT 'Standard shipping rate applies';

Unlike braces in C-style languages, BEGIN/END is only strictly required to group multiple statements under one IF/WHILE — a single statement doesn't need it, as the ELSE branch above shows. Many teams require BEGIN/END everywhere regardless, purely for consistency and to avoid a bug where someone adds a second statement to a branch and forgets it silently falls outside the IF.

Practice tip: Take the WHILE loop example above and rewrite its logic (printing iteration numbers) is fine as a loop — it's genuinely sequential output. But then find a real "loop through rows and update each one" scenario in your own head and consciously rewrite it as a single set-based statement instead. That translation instinct is worth more than memorizing WHILE syntax.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it's published, and share it with a developer who'd find it useful.

First Name

Last Name

Please verify that you are human.

Email address:

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.
July 18, 2026

TRY/CATCH Error Handling in SQL Server: THROW vs RAISERROR

Production T-SQL needs to fail gracefully, not just fail. An unhandled error in the middle of a multi-step operation can leave data in a half-finished state — TRY/CATCH, paired with transactions (covered in full in Chapter 9), is how you prevent that.

The Pattern

BEGIN TRY
    UPDATE dbo.Product SET stock_qty = stock_qty - 10 WHERE product_id = 2;
    IF (SELECT stock_qty FROM dbo.Product WHERE product_id = 2) < 0
        THROW 51000, 'Stock quantity cannot go negative.', 1;
END TRY
BEGIN CATCH
    PRINT 'Error caught: ' + ERROR_MESSAGE();
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
END CATCH;

Code inside BEGIN TRY ... END TRY runs normally. The instant any statement in that block raises an error, execution jumps immediately to BEGIN CATCH ... END CATCH — the rest of the TRY block is skipped entirely, similar to try/catch in C#, Java, or Python, but with SQL-Server-specific error inspection functions.

THROW vs RAISERROR

THROW is preferred — it's simpler, and with no arguments inside a CATCH block, it correctly preserves the original error's number, severity, and state when re-thrown. Reach for RAISERROR only when you need its legacy formatting (%s/%d placeholders) or must support very old SQL Server versions.

-- Re-throwing the ORIGINAL caught error, unchanged, after logging it:
BEGIN TRY
    SELECT 1/0; -- deliberate divide-by-zero to trigger an error
END TRY
BEGIN CATCH
    PRINT 'Logged: ' + ERROR_MESSAGE();
    THROW; -- bare THROW re-raises the exact original error
END CATCH;

Common mistake: Calling THROW with your own custom message/number when you meant to just re-raise the original error for the caller to see. A bare THROW; (no arguments) inside CATCH re-throws exactly what was caught — THROW 50000, 'Something failed', 1; replaces it with a brand-new, less specific error that loses the original diagnostic detail.

The Error Functions Toolkit

Function	Returns
`ERROR_NUMBER()`	The error's numeric code
`ERROR_MESSAGE()`	The human-readable error text
`ERROR_LINE()`	Line number where the error occurred
`ERROR_PROCEDURE()`	Procedure/function name, NULL if ad-hoc
`ERROR_SEVERITY()`	Severity level (11-19 typical for handleable errors)
`ERROR_STATE()`	A custom state number you can use to distinguish similar errors

All six are only valid inside a CATCH block — called anywhere else, they simply return NULL, since there's no error context to describe.

Nesting: A TRY/CATCH Inside a CATCH

BEGIN TRY
    UPDATE dbo.Product SET stock_qty = stock_qty - 10 WHERE product_id = 2;
END TRY
BEGIN CATCH
    BEGIN TRY
        INSERT INTO dbo.ErrorLog (error_message, logged_at) VALUES (ERROR_MESSAGE(), SYSDATETIME());
    END TRY
    BEGIN CATCH
        PRINT 'Even the error logging failed — this is genuinely bad, escalate.';
    END CATCH;
    THROW;
END CATCH;

This is a real, defensible pattern in production code: log the error to a table for later diagnosis, but wrap the logging itself in its own TRY/CATCH — you don't want a failure in your error-logging code to mask or replace the original error.

Practice tip: Trigger three different real errors on purpose — a divide by zero, a constraint violation, and a THROW with a custom message — and print all six ERROR_ functions for each inside a CATCH block. Seeing how the values differ across error types builds real intuition faster than reading the reference table above.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it's published, and share it with a developer who'd find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

July 18, 2026

Build a Complete SQL Server Database From Scratch: A Capstone Project Walkthrough

Everything from the fundamentals track comes together here — no new syntax, just applying what you already know (data types, DDL/DML, queries, aggregates, joins, and constraints) to a realistic, slightly underspecified brief, the way a real task at work actually arrives. This lesson gives you the brief, the schema skeleton, and the design decisions to wrestle with — not the finished answer. Building it yourself, including getting some parts wrong first, is the actual point.

The Brief: BookNook

Design and build a database for a small online bookstore. It needs to track books, authors, customers, and orders.

Author — name, country
Book — title, price, publish_year, foreign key to Author
Customer — name, unique email
CustomerOrder — customer_id (FK), order_date, status
OrderItem — the junction table connecting orders to books, since an order can contain many books and a book can appear in many orders

The Schema, Visualized

OrderItem is the piece most beginners miss on their first attempt — a many-to-many relationship (Book ↔ Order) always resolves through a junction table like this, never a direct link between the two. This is exactly the Enrollment pattern from Chapter 5, applied to a new domain.

A Skeleton to Start From — You Fill In the Constraints

Deliberately incomplete: the columns are given, but the exact PK/FK/CHECK/DEFAULT choices are yours to decide and justify, based on everything Chapters 2 and 6 covered.

CREATE TABLE dbo.Author (
    author_id   INT IDENTITY(1,1) PRIMARY KEY,
    full_name   NVARCHAR(100) NOT NULL,
    country     NVARCHAR(50)  NOT NULL
);

CREATE TABLE dbo.Book (
    book_id       INT IDENTITY(1,1) PRIMARY KEY,
    title         NVARCHAR(200) NOT NULL,
    author_id     INT NOT NULL REFERENCES dbo.Author(author_id),
    price         DECIMAL(8,2)  NOT NULL, -- what CHECK belongs here?
    publish_year  INT NOT NULL
);

CREATE TABLE dbo.Customer (
    customer_id  INT IDENTITY(1,1) PRIMARY KEY,
    full_name    NVARCHAR(100) NOT NULL,
    email        NVARCHAR(100) NOT NULL -- what constraint makes this genuinely unique?
);

CREATE TABLE dbo.CustomerOrder (
    order_id     INT IDENTITY(1,1) PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES dbo.Customer(customer_id),
    order_date   DATE NOT NULL, -- what DEFAULT saves you typing this every time?
    status       NVARCHAR(20) NOT NULL -- what DEFAULT status makes sense for a brand-new order?
);

CREATE TABLE dbo.OrderItem (
    order_id    INT NOT NULL REFERENCES dbo.CustomerOrder(order_id),
    book_id     INT NOT NULL REFERENCES dbo.Book(book_id),
    quantity    INT NOT NULL, -- what CHECK prevents a nonsensical quantity?
    unit_price  DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_id, book_id) -- why a composite key here, specifically?
);

A Real Design Decision You’ll Have to Make

Should OrderItem.unit_price duplicate Book.price, or should you just JOIN to Book for the price at query time? Prices change over time — what should an order from six months ago show, today’s price or the price actually paid at purchase? This is a genuine, common denormalization decision (echoing Chapter 6’s normalization lesson) — not a mistake to avoid. The right answer here is almost certainly to duplicate it: a historical order should show what was actually paid, not today’s price. Storing it directly on OrderItem is deliberate denormalization for a good reason, exactly the kind of exception the normalization lesson told you to expect.

What Your Submission Needs

All five CREATE TABLE statements with appropriate PK/FK/CHECK/DEFAULT constraints — fill in every blank left above, with a one-line comment justifying each constraint choice
Realistic sample data — at least 4 authors, 8 books, 5 customers, 6 orders, 10 order items
A query showing each customer’s total spend across all orders (needs JOIN + GROUP BY + SUM)
A query showing the best-selling book by total quantity ordered (needs JOIN + GROUP BY + SUM + ORDER BY + TOP)
A query showing authors who’ve never had a book ordered — careful with the LEFT JOIN + WHERE trap from Chapter 5

Common mistake to watch for yourself making: Query #5 (authors never ordered) is a two-hop LEFT JOIN — Author to Book to OrderItem — and it’s very easy to accidentally write a WHERE clause on OrderItem that silently turns your LEFT JOINs back into INNER JOINs, making every author with zero orders vanish from the result instead of showing up with NULLs. If your result set looks suspiciously short, this is the first thing to check.

Self-Check Before You Consider It Done

Check	Why it matters
Try inserting an OrderItem with a book_id that doesn’t exist	Confirms your FK constraint actually works, not just that it compiles
Try inserting a negative price or zero quantity	Confirms your CHECK constraints catch nonsensical values
Run query #5 and manually verify one “never ordered” author against your raw data	The single best way to catch the LEFT JOIN + WHERE bug before it ships

Stretch Goal: Deploy It for Real

Everything above works identically on your local install — but try creating this exact database on Azure SQL Database or AWS RDS (Chapter 0) instead of locally. All the same CREATE TABLE and INSERT statements work unchanged; only how you connect changes.

What comes next: Once this capstone is genuinely working — constraints tested, all five queries returning correct results you’ve manually verified — you have everything SQL Server for Developers & DBAs assumes you already know. That course picks up exactly here: stored procedures, functions, triggers, transactions, and real performance tuning against schemas like this one.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site. Finished this capstone? You’re ready for SQL Server for Developers & DBAs.

July 18, 2026

Database Normalization Explained: 1NF, 2NF, and 3NF in Plain English

Normalization is the discipline of structuring tables to minimize duplicate data and avoid update anomalies. It’s also the concept that quietly justifies almost every schema decision made throughout this course — why Trip referenced Driver instead of repeating the driver’s name on every row, why phone numbers got their own table. Here’s what the first three normal forms actually mean, without the textbook jargon, and a worked example showing the actual bugs an unnormalized table produces.

The Three Rules

Form	Rule, in plain language
1NF	Every column holds one atomic value — no comma-separated lists crammed into a cell
2NF	Every non-key column depends on the whole primary key, not just part of it (only matters when the key has multiple columns)
3NF	Every non-key column depends only on the key — not on another non-key column

1NF in Practice

-- VIOLATES 1NF: multiple phone numbers crammed into one column
-- phone_numbers = '555-1234, 555-5678'  ❌

-- FIXED: one row per phone number in a related table
CREATE TABLE dbo.ContactPhone (
    phone_id  INT IDENTITY(1,1) PRIMARY KEY,
    staff_id  INT NOT NULL REFERENCES dbo.Staff(staff_id),
    phone     VARCHAR(20) NOT NULL
);

The comma-separated version isn’t just stylistically ugly — it’s functionally broken. You can’t easily search “who has this phone number,” can’t enforce a phone number is only associated with one person, and any query trying to count phone numbers per employee needs fragile string-splitting logic instead of a simple COUNT(*) ... GROUP BY.

2NF: A Worked Example With a Composite Key

-- VIOLATES 2NF: composite key is (order_id, product_id), but product_name
-- depends ONLY on product_id, not on the full key
CREATE TABLE dbo.OrderLine_Bad (
    order_id      INT,
    product_id    INT,
    product_name  NVARCHAR(100), -- ❌ repeated on every order line for this product
    quantity      INT,
    PRIMARY KEY (order_id, product_id)
);

-- FIXED: product_name moves to its own table, keyed by product_id alone
CREATE TABLE dbo.Product (
    product_id    INT PRIMARY KEY,
    product_name  NVARCHAR(100) NOT NULL
);
CREATE TABLE dbo.OrderLine (
    order_id    INT,
    product_id  INT REFERENCES dbo.Product(product_id),
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

In the “bad” version, if a product gets renamed, you must update every single order line that ever referenced it — miss one, and your data now disagrees with itself about the product’s name. That’s the specific failure 2NF prevents: a partial dependency (product_name depending on only part of the composite key) causing update anomalies.

3NF: Transitive Dependencies

-- VIOLATES 3NF: department_name depends on department_id, not directly on staff_id (the key)
CREATE TABLE dbo.Staff_Bad (
    staff_id          INT PRIMARY KEY,
    full_name         NVARCHAR(100),
    department_id     INT,
    department_name   NVARCHAR(50) -- ❌ depends on department_id, a NON-key column
);

-- FIXED: department_name lives only in Department, referenced by FK
CREATE TABLE dbo.Department (department_id INT PRIMARY KEY, department_name NVARCHAR(50) NOT NULL);
CREATE TABLE dbo.Staff_Good (
    staff_id       INT PRIMARY KEY,
    full_name      NVARCHAR(100) NOT NULL,
    department_id  INT NOT NULL REFERENCES dbo.Department(department_id)
);

Same failure mode as 2NF, one step removed: department_name “transitively” depends on the key through department_id, rather than directly. Rename a department, and every staff row in the “bad” table needs updating in lockstep, or the data silently contradicts itself.

Normalized vs Denormalized, Visualized

Real schemas often deliberately break strict normalization for performance reasons — called denormalization. A reporting table might intentionally store department_name alongside staff data to avoid a JOIN on every single dashboard query, accepting the update-anomaly risk as a worthwhile tradeoff because that data changes rarely and is read constantly. Know the rules well enough to break them on purpose, with a clear reason, not by accident because you didn’t recognize the dependency in the first place.

Common mistake: Treating normalization as an absolute rule to maximize everywhere. Over-normalizing a schema that’s read far more often than it’s written can hurt real-world performance for no real correctness benefit — normalization is a tool for a specific problem (update anomalies from duplicated data), not a virtue in itself.

Practice tip: Take the “bad” OrderLine and Staff examples above, actually create them, insert a few rows with intentionally repeated product_name/department_name values, then try to make them inconsistent with an UPDATE that only touches one row. Watch how easy it is to accidentally create disagreeing data — that hands-on experience is worth more than memorizing the three rules.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.

July 18, 2026

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK: SQL Server Constraints Explained

You’ve met each of these individually already — PK and FK in Chapter 5, NOT NULL and DEFAULT in Chapter 2. This lesson brings the full constraint family together in one place, adds UNIQUE and CHECK, and is explicit about exactly what each one guarantees and how it fails.

CREATE TABLE dbo.Department (
    department_id   INT IDENTITY(1,1) PRIMARY KEY,
    department_name NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE dbo.Staff (
    staff_id       INT IDENTITY(1,1) PRIMARY KEY,
    email          NVARCHAR(100) NOT NULL UNIQUE,
    department_id  INT NOT NULL REFERENCES dbo.Department(department_id),
    salary         DECIMAL(10,2) NOT NULL CHECK (salary > 0)
);

What Each One Guarantees

Constraint	Guarantees
PRIMARY KEY	Uniquely identifies every row; implies NOT NULL + UNIQUE; a table can have only one
FOREIGN KEY	Value must exist in the referenced table’s PK (or be NULL, if the FK column allows it)
UNIQUE	No two rows share this value — but unlike PK, allows one NULL (NULL isn’t considered equal to another NULL, even here), and a table can have several UNIQUE constraints
CHECK	Value must satisfy a boolean expression, evaluated on every INSERT/UPDATE

Watching Them Do Their Job

INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('bad@co.com', 999, 50000);
-- Error: FOREIGN KEY constraint... department_id 999 doesn't exist

INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('bad2@co.com', 1, -100);
-- Error: CHECK constraint "CK_Staff_salary" violated

INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('taken@co.com', 1, 60000);
INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('taken@co.com', 1, 65000);
-- Error: Violation of UNIQUE KEY constraint... duplicate email

Naming Your Constraints on Purpose

-- Unnamed — SQL Server auto-generates a name like CK__Staff__salary__1234ABCD
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0)

-- Named explicitly — readable in error messages and easy to ALTER/DROP later
CONSTRAINT CK_Staff_PositiveSalary CHECK (salary > 0)

Practice tip: Always name your own constraints in real schemas. “Violation of CHECK constraint CK_Staff_PositiveSalary” tells you and your teammates exactly what rule broke; an auto-generated name with a random suffix tells you nothing without looking it up.

Adding a Constraint to an Existing Table

-- The table already exists; add the rule after the fact
ALTER TABLE dbo.Staff ADD CONSTRAINT CK_Staff_ValidEmail CHECK (email LIKE '%_@_%._%');

-- Temporarily allow existing bad rows to be re-checked separately (rare, use with care)
ALTER TABLE dbo.Staff WITH NOCHECK ADD CONSTRAINT CK_Staff_PositiveSalary CHECK (salary > 0);

Common mistake: Adding a CHECK constraint with WITH NOCHECK to skip validating existing rows, then assuming the constraint is fully trustworthy going forward. It isn’t — existing violating rows stay in the table untouched, and the constraint is marked “not trusted,” which means the query optimizer can’t safely use it to simplify certain queries either. Only use WITH NOCHECK when you deliberately intend to clean up existing violations separately, and re-validate with WITH CHECK CHECK CONSTRAINT ALL once you have.

What Happens When You Try to Delete a Constraint’s “Reason”

-- Trying to drop a department that staff still reference:
DELETE FROM dbo.Department WHERE department_id = 1;
-- Error: The DELETE statement conflicted with the REFERENCE constraint

-- The correct sequence: remove or reassign dependents first
UPDATE dbo.Staff SET department_id = 2 WHERE department_id = 1;
DELETE FROM dbo.Department WHERE department_id = 1;

Practice tip: Design the full Staff/Department schema above yourself from a blank query window, including at least one intentional constraint violation for each of PK, FK, UNIQUE, and CHECK, and read each actual error message SQL Server gives you. Recognizing these four error shapes on sight is a genuinely useful, permanent skill.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.

July 18, 2026

Why Push Business Rules Into Your SQL Server Schema, Not Just the App
← Full Course Index

Ch. 6: Constraints

1. PK, FK, UNIQUE, CHECK
2. Business Rules via Constraints
3. Normalization Basics
Why Push Business Rules Into Your SQL Server Schema, Not Just the App

Constraints aren’t just data hygiene — they’re how you encode real business rules directly into the schema, so they can never be silently bypassed by a buggy application, a second app added later that talks to the same database, or a stray ad-hoc UPDATE run during an incident.

Encoding a Real Rule

-- Rule: an order's ship date can never be before its order date CREATE TABLE dbo.CustomerOrder ( order_id INT IDENTITY(1,1) PRIMARY KEY, order_date DATE NOT NULL, ship_date DATE NULL CHECK (ship_date IS NULL OR ship_date >= order_date), total_usd DECIMAL(10,2) NOT NULL CHECK (total_usd >= 0) );

-- Proving the rule holds, not just reading about it: INSERT INTO dbo.CustomerOrder (order_date, ship_date, total_usd) VALUES ('2026-01-10', '2026-01-05', 49.99); -- Error: CHECK constraint violated — shipped 5 days BEFORE it was ordered, correctly rejected

A CHECK Constraint Spanning Multiple Columns

CHECK isn’t limited to validating one column against a literal — it can compare columns on the same row to each other, as shown above (ship_date against order_date). Another common shape:

CREATE TABLE dbo.Promotion ( promotion_id INT IDENTITY(1,1) PRIMARY KEY, starts_on DATE NOT NULL, ends_on DATE NOT NULL, CHECK (ends_on > starts_on) );

This kind of cross-column rule is exactly the class of business logic that’s easy to forget to validate in one code path of an application (a bulk-import script, an admin panel, an API endpoint added six months later by someone unfamiliar with the original rule) but structurally impossible to skip once it lives in the schema.

The Last Line of Defense

Applications get replaced, have bugs, or get bypassed by a direct database script during an incident. A CHECK constraint at the database layer is enforced no matter what wrote the data — it’s the guarantee application code alone can never fully provide. This is sometimes summarized as “defense in depth”: validate in the application for a fast, friendly error message to the user, and validate in the database as the guarantee that actually holds under all circumstances.

Where This Doesn’t Reach — And What Does

CHECK constraints are limited to logic expressible within a single row’s own columns — they can’t reference other tables or aggregate across rows. “A department can’t have more than 20 staff” or “an order’s total must match the sum of its line items” needs a different tool: a trigger, or logic in a stored procedure. That’s a deliberate scope boundary you’ll meet by name (constraints vs. triggers vs. procedures) as a full decision framework in SQL Server for Developers & DBAs — for now, the key lesson is simply that single-row rules belong in CHECK constraints, full stop, because nothing enforces them more reliably.

Practice tip: Look at any form you’ve filled out recently (a signup form, a checkout flow) and identify one validation rule it enforces. Ask yourself: is that rule also enforced at the database level, or only in that one form? If you can imagine a second way data could enter that table — an admin tool, a script, a different app — that’s exactly the gap a CHECK constraint closes.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

First Name

Last Name

Please verify that you are human.

Email address:

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.
July 18, 2026
UNION vs UNION ALL in SQL Server, Plus Your First Subquery
← Full Course Index

Ch. 5: Multiple Tables

1. Primary & Foreign Keys
2. JOIN Types
3. UNION & Subqueries
4. Temp Tables: First Look
UNION vs UNION ALL in SQL Server, Plus Your First Subquery

JOINs combine tables side by side, adding columns. UNION combines result sets top to bottom, stacking rows — a fundamentally different kind of combination, useful whenever two separate queries produce compatible-shaped rows you want as one result. Subqueries, the second half of this lesson, let a query’s WHERE clause be driven by the result of an entirely separate query.

UNION Combines and Deduplicates

SELECT full_name, 'Austin driver' AS note FROM dbo.Driver WHERE city = 'Austin' UNION ALL SELECT full_name, 'High earner' AS note FROM dbo.Driver WHERE driver_id IN (SELECT driver_id FROM dbo.Trip WHERE fare_usd > 30);

Every SELECT in a UNION must return the same number of columns, in compatible types, in the same order — the column names in the final result come from the first SELECT only. This is worth testing directly:

-- FAILS: mismatched column counts SELECT full_name FROM dbo.Driver UNION ALL SELECT full_name, city FROM dbo.Driver; -- Msg 205: All queries combined using a UNION, INTERSECT or EXCEPT operator must have -- an equal number of expressions in their target lists.

UNION vs UNION ALL

UNION runs an implicit dedup step (conceptually similar to SELECT DISTINCT applied to the combined result) — real work that costs real time on large result sets. If you know there’s no overlap between the two queries (as in the example above, since a driver can’t simultaneously fail and pass the same filter), or duplicates are genuinely fine for your use case, UNION ALL is the better default. Reach for plain UNION only when you specifically need duplicates removed.

Two More Set Operators, Briefly

-- INTERSECT: only rows present in BOTH result sets SELECT city FROM dbo.Driver INTERSECT SELECT city FROM dbo.Driver WHERE driver_id > 2; -- EXCEPT: rows in the first result set but NOT the second SELECT city FROM dbo.Driver EXCEPT SELECT city FROM dbo.Driver WHERE driver_id > 2;

Same column-matching rules as UNION apply. These are less common day-to-day than UNION ALL, but genuinely useful for comparison/reconciliation queries — “what’s in this dataset that isn’t in that one.”

Your First Subquery

SELECT full_name FROM dbo.Driver WHERE driver_id IN ( SELECT driver_id FROM dbo.Trip WHERE distance_km > 20 );

The inner SELECT driver_id FROM dbo.Trip WHERE distance_km > 20 runs first (conceptually), producing a list of IDs the outer query then filters against. This pattern — nesting a query inside another’s WHERE clause — is one you’ll use constantly, and it comes in a few distinct shapes:

-- Scalar subquery: returns exactly one value, usable anywhere a single value fits SELECT full_name FROM dbo.Driver WHERE driver_id = (SELECT TOP 1 driver_id FROM dbo.Trip ORDER BY fare_usd DESC); -- Correlated subquery: references the OUTER query's row, re-evaluated per row SELECT full_name FROM dbo.Driver d WHERE EXISTS (SELECT 1 FROM dbo.Trip t WHERE t.driver_id = d.driver_id AND t.fare_usd > 25);

That last one — a correlated subquery using EXISTS — is worth flagging early even though it looks more advanced: it’s generally the safer, often faster alternative to IN for “does at least one matching row exist” checks, and unlike NOT IN (Chapter 3), NOT EXISTS handles NULLs correctly with no surprise gotcha.

Practice tip: Rewrite the very first example in this lesson (drivers with any trip over 20km) using EXISTS instead of IN, and confirm you get the same result. Getting comfortable moving between the two forms pays off enormously once query performance becomes a topic in the advanced course.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

First Name

Last Name

Please verify that you are human.

Email address:

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.
July 18, 2026
SQL Server Temp Tables: A First Look Before You Need the Full Picture
← Full Course Index

Ch. 5: Multiple Tables

1. Primary & Foreign Keys
2. JOIN Types
3. UNION & Subqueries
4. Temp Tables: First Look
SQL Server Temp Tables: A First Look Before You Need the Full Picture

Sometimes a problem is genuinely easier to solve in two steps than one giant nested query. Temp tables are SQL Server’s answer to “I need somewhere to put an intermediate result while I keep working.”

A Local Temp Table in Action

CREATE TABLE #HighValueTrips ( trip_id INT, fare_usd DECIMAL(8,2) ); INSERT INTO #HighValueTrips SELECT trip_id, fare_usd FROM dbo.Trip WHERE fare_usd > 20; SELECT * FROM #HighValueTrips; DROP TABLE #HighValueTrips;

Notice this is genuinely a real table — it has its own CREATE TABLE, accepts INSERT, and can be queried, filtered, and even joined to other tables exactly like a permanent one, for as long as your session lasts.

What Makes It “Temporary”

A local temp table (prefixed with #) physically lives in the special system database tempdb, not your regular database — but is visible only to the session that created it, and is automatically cleaned up when that session ends, or you can drop it explicitly as shown above. Two sessions can both create a table named #Scratch at the same time without any conflict; SQL Server keeps them completely separate internally.

A Realistic Two-Step Use Case

-- Step 1: capture an expensive-to-compute intermediate result once SELECT driver_id, COUNT(*) AS trip_count, SUM(fare_usd) AS total_earned INTO #DriverSummary FROM dbo.Trip GROUP BY driver_id; -- Step 2: reuse it multiple times without recomputing the aggregation SELECT * FROM #DriverSummary WHERE trip_count > 5; SELECT d.full_name, s.total_earned FROM dbo.Driver d JOIN #DriverSummary s ON d.driver_id = s.driver_id ORDER BY s.total_earned DESC;

SELECT ... INTO creates the temp table and populates it in one statement, inferring column types automatically — a common shortcut once you’re comfortable with the explicit CREATE TABLE form shown earlier.

Why Not Just Use a Bigger Subquery?

You often could. The tradeoff: a temp table computes its result once and lets you reuse and re-query it as many times as needed; a subquery or CTE re-runs its logic each time it’s referenced (with some caveats the advanced course covers). For a genuinely expensive intermediate calculation you need to reuse several times in a longer script, a temp table can be both clearer to read and faster to run.

Just the Beginning

This is a preview — the full comparison of local temp tables, table variables, and global temp tables (prefixed ##, visible across all sessions), including exactly when each one is the right tool for a given job and their real performance differences, is a dedicated chapter (Chapter 3) in SQL Server for Developers & DBAs. For now, know that temp tables exist and behave like session-scoped scratch space you can CREATE, INSERT into, query, and DROP just like any other table.

Practice tip: Rebuild the two-step example above from memory, then try querying #DriverSummary from a brand-new query window/tab in the same tool — you’ll get an “invalid object name” error, since a fresh window is a fresh session. That’s the session-scoping rule made concrete.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

First Name

Last Name

Please verify that you are human.

Email address:

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.
July 18, 2026
SQL Server JOIN Types Explained: INNER, LEFT, RIGHT, FULL, CROSS, SELF
← Full Course Index

Ch. 5: Multiple Tables

1. Primary & Foreign Keys
2. JOIN Types
3. UNION & Subqueries
4. Temp Tables: First Look
SQL Server JOIN Types Explained: INNER, LEFT, RIGHT, FULL, CROSS, SELF

Joining tables is where SQL starts feeling genuinely powerful — and where a subtle mistake silently produces wrong results with zero errors. This lesson covers all six JOIN types with real output differences, then spends real time on the single bug that catches nearly everyone at least once.

The Core Two

-- INNER JOIN: only rows that match in both tables SELECT d.full_name, t.fare_usd FROM dbo.Driver d INNER JOIN dbo.Trip t ON d.driver_id = t.driver_id; -- LEFT JOIN: all rows from the left table, matched rows from the right (NULL if no match) SELECT d.full_name, t.fare_usd FROM dbo.Driver d LEFT JOIN dbo.Trip t ON d.driver_id = t.driver_id;

Concretely, if a driver named ‘Amir Khan’ exists but has never had a trip logged: INNER JOIN omits him from the result entirely; LEFT JOIN still shows one row for him, with fare_usd as NULL. That NULL is the entire reason LEFT JOIN exists — it’s how you answer “show me every driver, including ones with zero trips.”

The Rest of the Set

-- RIGHT JOIN: mirror of LEFT — all rows from the right table instead SELECT d.full_name, t.fare_usd FROM dbo.Trip t RIGHT JOIN dbo.Driver d ON d.driver_id = t.driver_id; -- FULL OUTER JOIN: everything from both sides, matched where possible SELECT d.full_name, t.trip_id FROM dbo.Driver d FULL OUTER JOIN dbo.Trip t ON d.driver_id = t.driver_id; -- CROSS JOIN: every combination (Cartesian product) — rarely intentional by accident SELECT d.full_name, x.label FROM dbo.Driver d CROSS JOIN (VALUES ('Gold'),('Silver')) AS x(label); -- SELF JOIN: a table joined to itself — e.g. drivers sharing a city SELECT d1.full_name, d2.full_name, d1.city FROM dbo.Driver d1 INNER JOIN dbo.Driver d2 ON d1.city = d2.city AND d1.driver_id < d2.driver_id;

In practice, RIGHT JOIN is rarely used on purpose — anything expressible with RIGHT JOIN can be rewritten as a LEFT JOIN by swapping which table comes first, and most style guides prefer that for consistency. FULL OUTER JOIN is genuinely useful for reconciliation tasks ("what's in table A but not B, and vice versa, in one query"). CROSS JOIN's real, non-accidental use case is generating combinations — like every product paired with every size, before either exists in a real order.

The SELF JOIN's d1.driver_id < d2.driver_id condition deserves its own note: without it, every pair of same-city drivers would appear twice (Amir/Priya and Priya/Amir), plus every driver paired with themselves. The inequality keeps exactly one direction of each unique pair.

The Bug That Gets Everyone at Least Once

Using LEFT JOIN correctly, then adding a WHERE filter on the right-hand table's column, silently turns it back into an INNER JOIN:

-- BUG: this discards the NULL rows LEFT JOIN was specifically trying to preserve SELECT d.full_name, t.fare_usd FROM dbo.Driver d LEFT JOIN dbo.Trip t ON d.driver_id = t.driver_id WHERE t.fare_usd > 20;

NULL fails the > 20 comparison (from Chapter 3's three-valued-logic rule), so unmatched drivers with no trips disappear from the result — exactly what LEFT JOIN was meant to prevent. The query runs without error and looks completely reasonable; you only notice something's wrong when a driver you know exists is mysteriously missing from a report.

-- The fix: move the condition into the ON clause instead of WHERE SELECT d.full_name, t.fare_usd FROM dbo.Driver d LEFT JOIN dbo.Trip t ON d.driver_id = t.driver_id AND t.fare_usd > 20; -- Now unmatched drivers still appear (fare_usd NULL); only trips are filtered before the join completes

The rule to memorize: a WHERE condition on the "preserved" side's columns filters the final result, potentially undoing your LEFT JOIN. The same condition inside the ON clause filters before the join decides what counts as a match, which is almost always what you actually want when the goal is "keep all drivers, but only join in their high-value trips."

Practice tip: Run the buggy version and the fixed version side by side and count the rows returned by each. Seeing the actual row-count difference with your own data makes this rule permanent in a way that reading about it doesn't.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it's published, and share it with a developer who'd find it useful.

First Name

Last Name

Please verify that you are human.

Email address:

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.
July 18, 2026

Tag: SQL Server

Dynamic SQL in SQL Server: sp_executesql and Avoiding SQL Injection

The Safe Pattern

How Injection Actually Happens

The Rule, Visualized

QUOTENAME Isn’t Optional Even for “Trusted” Input

Why Use Dynamic SQL At All?

Enjoyed this?

T-SQL Variables, Batches, and Control-of-Flow: Beyond Basic Queries

Declaring and Using Variables

The Batch-Scoping Trap

WHILE Loops: A Last Resort, Not a First Instinct

BEGIN/END Blocks and Nesting

Enjoyed this?

TRY/CATCH Error Handling in SQL Server: THROW vs RAISERROR

The Pattern

THROW vs RAISERROR

The Error Functions Toolkit

Nesting: A TRY/CATCH Inside a CATCH

Enjoyed this?

Build a Complete SQL Server Database From Scratch: A Capstone Project Walkthrough

The Brief: BookNook

The Schema, Visualized

A Skeleton to Start From — You Fill In the Constraints

A Real Design Decision You’ll Have to Make

What Your Submission Needs

Self-Check Before You Consider It Done

Stretch Goal: Deploy It for Real

Enjoyed this?

Database Normalization Explained: 1NF, 2NF, and 3NF in Plain English

The Three Rules

1NF in Practice

2NF: A Worked Example With a Composite Key

3NF: Transitive Dependencies

Normalized vs Denormalized, Visualized

Enjoyed this?

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK: SQL Server Constraints Explained

What Each One Guarantees

Watching Them Do Their Job

Naming Your Constraints on Purpose

Adding a Constraint to an Existing Table

What Happens When You Try to Delete a Constraint’s “Reason”

Enjoyed this?

Why Push Business Rules Into Your SQL Server Schema, Not Just the App

Encoding a Real Rule

A CHECK Constraint Spanning Multiple Columns

The Last Line of Defense

Where This Doesn’t Reach — And What Does

Enjoyed this?

UNION vs UNION ALL in SQL Server, Plus Your First Subquery

UNION Combines and Deduplicates

UNION vs UNION ALL

Two More Set Operators, Briefly

Your First Subquery

Enjoyed this?

SQL Server Temp Tables: A First Look Before You Need the Full Picture

A Local Temp Table in Action

What Makes It “Temporary”

A Realistic Two-Step Use Case

Why Not Just Use a Bigger Subquery?

Just the Beginning

Enjoyed this?

SQL Server JOIN Types Explained: INNER, LEFT, RIGHT, FULL, CROSS, SELF

The Core Two

The Rest of the Set

The Bug That Gets Everyone at Least Once

Enjoyed this?