Category: SQL Server Fundamentals

Beginner SQL Server tutorials: data manipulation, queries, aggregate functions, multiple tables, constraints.

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

Primary Keys and Foreign Keys in SQL Server: How Tables Actually Connect

Everything up to this chapter queried one table at a time. Real schemas are almost never one table — they’re a web of tables connected by keys, and understanding exactly how that connection is enforced (not just “it points to the other table”) is what makes JOINs in the next lesson click instead of feeling like memorized syntax.

Setup — a driver/trip example to follow along:

CREATE TABLE dbo.Driver (
    driver_id   INT IDENTITY(1,1) PRIMARY KEY,
    full_name   NVARCHAR(100) NOT NULL,
    city        NVARCHAR(50)  NOT NULL
);
CREATE TABLE dbo.Trip (
    trip_id       INT IDENTITY(1,1) PRIMARY KEY,
    driver_id     INT NOT NULL REFERENCES dbo.Driver(driver_id),
    distance_km   DECIMAL(6,2) NOT NULL,
    fare_usd      DECIMAL(8,2) NOT NULL
);

The Relationship, Visualized

A primary key (PK) uniquely identifies each row (driver_id in Driver) — SQL Server automatically creates a unique index behind every primary key, which is why lookups by PK are fast by default, before you’ve even thought about indexing. A foreign key (FK) is a column in one table that points to a primary key in another (Trip.driver_id → Driver.driver_id).

Watching the Constraint Actually Enforce Something

-- This fails — driver_id 999 doesn't exist in Driver:
INSERT INTO dbo.Trip (driver_id, distance_km, fare_usd) VALUES (999, 5.2, 12.50);
-- Msg 547: The INSERT statement conflicted with the FOREIGN KEY constraint ...

-- This fails too — you can't delete a driver who still has trips referencing them:
INSERT INTO dbo.Driver (full_name, city) VALUES ('Amir Khan', 'Austin');
INSERT INTO dbo.Trip (driver_id, distance_km, fare_usd) VALUES (1, 8.0, 18.00);
DELETE FROM dbo.Driver WHERE driver_id = 1;
-- Msg 547: The DELETE statement conflicted with the REFERENCE constraint ...

This is the entire point of a foreign key: SQL Server enforces that you can’t log a trip for a driver who doesn’t exist, and can’t delete a driver out from under existing trips — it rejects the operation outright, rather than silently leaving a trip pointing at nothing (an “orphaned row”). Without this constraint, that kind of data corruption is entirely possible and often goes unnoticed until a report breaks months later.

Three Relationship Shapes

Shape	Example	How it’s modeled
One-to-many (1:N)	One driver, many trips	A foreign key on the “many” side, as shown above
Many-to-many (N:N)	Many students enroll in many courses	A junction table in between, holding two foreign keys — e.g. Student ↔ Course via Enrollment
One-to-one (1:1)	Employee ↔ EmployeeConfidentialDetails	A foreign key with a UNIQUE constraint added — rare, often used to split sensitive columns into a separately-secured table

A Many-to-Many Example, Concretely

CREATE TABLE dbo.Student (student_id INT IDENTITY PRIMARY KEY, name NVARCHAR(100) NOT NULL);
CREATE TABLE dbo.Course (course_id INT IDENTITY PRIMARY KEY, title NVARCHAR(100) NOT NULL);

-- The junction table: one row per student-course PAIR, with a composite primary key
CREATE TABLE dbo.Enrollment (
    student_id INT NOT NULL REFERENCES dbo.Student(student_id),
    course_id  INT NOT NULL REFERENCES dbo.Course(course_id),
    enrolled_on DATE NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (student_id, course_id)
);

Neither Student nor Course has a foreign key pointing directly at the other — they can’t, since either side could relate to many rows on the other. The junction table’s composite primary key (both columns together) also does double duty: it prevents the same student from enrolling in the same course twice.

Practice tip: Sketch the junction table for a “many books can have many authors, many authors can write many books” relationship before the Chapter 7 capstone — you’ll build exactly this schema there for real.

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

System vs User-Defined Functions in SQL Server: A First Look

Every function you’ve used so far — COUNT, GETDATE, UPPER — ships with SQL Server itself. Soon (in the advanced course) you’ll write your own. Here’s the map before you get there, including why the choice between function types is a genuine engineering decision, not just a syntax preference.

Two Categories

Type	Example	Who defines it
System function	`GETDATE()`, `SUM()`, `UPPER()`	Built into SQL Server
Scalar UDF	`dbo.CalculateAge(birthdate)`	You write it, returns one value
Inline table-valued (iTVF)	`dbo.GetActiveStartups()`	You write it, returns a table, behaves like a parameterized view
Multi-statement TVF	`dbo.GetStartupReport(@year)`	You write it, builds a table procedurally, statement by statement

A Preview of What a Scalar UDF Looks Like

You’re not expected to write this yet, but seeing the shape demystifies it — it’s really just a named, reusable calculation:

CREATE FUNCTION dbo.CalculateAge (@birthdate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @birthdate, GETDATE());
END;

-- Once created, it's used exactly like a built-in function:
SELECT name, dbo.CalculateAge('1995-06-15') AS age FROM dbo.Startup;

Notice it slots directly into a SELECT list, just like UPPER() or DATEDIFF() — from the caller’s perspective, a well-written scalar UDF is indistinguishable from a system function. That’s exactly the point: it extends SQL Server’s function library with your own domain-specific logic.

Why the Choice Between These Types Actually Matters

This isn’t just a naming exercise. A scalar UDF called inside a WHERE clause against every row of a large table can quietly turn a query that should take milliseconds into one that takes seconds — because (in most SQL Server versions before 2019’s scalar UDF inlining improvements) the function executes once per row, outside the query optimizer’s usual cost-based reasoning. An inline TVF, by contrast, gets expanded directly into the surrounding query and optimized like ordinary SQL. This single distinction — can the optimizer see through it or not — is one of the most consequential real-world performance decisions a T-SQL developer makes, and it’s covered in full in the advanced course once you have the query-plan-reading skills to actually verify the difference yourself.

Where This Goes Next

Writing your own functions — and understanding the real performance tradeoffs between the three types — is a full topic in SQL Server for Developers & DBAs (Chapter 2 there is dedicated entirely to this). For now, the goal is simply recognizing that this whole other category exists, and that “which function type” is a real design decision, not an arbitrary label.

Practice tip: Next time you use a built-in function like DATEDIFF or ROUND, pause and ask: “if I had to write this myself as a scalar function, what would the body look like?” You already have enough T-SQL from this chapter to answer that for several of them — which is exactly the mental bridge to writing real UDFs later.

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

SQL Server Built-in Functions: String, Date, and Math Functions You’ll Use Daily

Beyond aggregates, SQL Server ships a huge library of scalar functions — functions that take input and return one value per row, rather than collapsing many rows into one. Here are the ones you’ll actually reach for constantly, with the specific edge cases that catch people off guard.

String Functions

SELECT UPPER(name), LOWER(name), LEN(name) FROM dbo.Startup;
SELECT CONCAT(name, ' (', industry, ')') AS label FROM dbo.Startup;
SELECT SUBSTRING(name, 1, 4) FROM dbo.Startup;
SELECT TRIM('  padded  ') AS cleaned;
SELECT REPLACE(name, 'Data', 'Info') FROM dbo.Startup;
SELECT LEFT(name, 3), RIGHT(name, 3) FROM dbo.Startup;

Common mistake: Using + to concatenate strings when one side might be NULL — first_name + ' ' + last_name returns NULL for the entire expression if either piece is NULL. CONCAT() treats NULL as an empty string instead, which is almost always what you actually want: CONCAT(first_name, ' ', last_name).

Date Functions

SELECT GETDATE() AS right_now;              -- current date + time
SELECT SYSDATETIME() AS right_now_precise;  -- higher precision, preferred in new code
SELECT YEAR(GETDATE()) AS current_year;
SELECT DATEDIFF(YEAR, '2020-01-01', GETDATE()) AS years_since_2020;
SELECT DATEADD(MONTH, 6, GETDATE()) AS six_months_from_now;
SELECT DATENAME(WEEKDAY, GETDATE()) AS day_name;  -- e.g. 'Friday'

Common mistake: DATEDIFF(YEAR, ...) counts calendar-year boundaries crossed, not full 365-day years. DATEDIFF(YEAR, '2020-12-31', '2021-01-01') returns 1, even though only one day actually passed — because a year boundary (Dec 31 → Jan 1) was crossed. This surprises almost everyone the first time they compute an “age” this way and get a value that’s off by one right around a birthday or anniversary.

Math and Rounding Functions

SELECT ROUND(funding_usd / 1000000.0, 2) AS funding_millions FROM dbo.Startup;
SELECT CEILING(4.1) AS rounds_up;   -- 5
SELECT FLOOR(4.9) AS rounds_down;   -- 4
SELECT ABS(-42) AS absolute_value;  -- 42

The Integer Division Trap

SELECT 7 / 2 AS wrong_answer;      -- 3, NOT 3.5 — both operands are INT, result truncates
SELECT 7.0 / 2 AS correct_answer;  -- 3.5 — forcing one operand to a decimal type fixes it
SELECT CAST(7 AS DECIMAL(10,2)) / 2 AS also_correct;

Common mistake: Dividing two INT columns and expecting a decimal result. SQL Server (like most languages) performs integer division when both operands are integers — the fractional part is silently discarded, not rounded, with no error or warning. This is a genuinely common source of “my percentage calculation shows 0” bugs. Always cast at least one side to a decimal type when division needs to be exact.

NULL-Handling and Conversion Functions

SELECT ISNULL(NULL, 'fallback value') AS demo;               -- returns 'fallback value'
SELECT COALESCE(NULL, NULL, 'third option') AS demo2;        -- returns 'third option'
SELECT CAST(funding_usd AS BIGINT) AS funding_rounded FROM dbo.Startup;
SELECT TRY_CAST('not a number' AS INT) AS safe_conversion;   -- returns NULL, not an error

TRY_CAST (and its cousin TRY_CONVERT) return NULL instead of throwing an error when a conversion fails — invaluable when converting messy, real-world data where you can’t guarantee every value is well-formed.

Quick Reference

Function	Does
`LEN()`	Character length of a string
`CONCAT()`	Joins strings, treating NULL as empty rather than poisoning the whole result
`DATEDIFF()`	Difference between two dates in a given unit — counts boundaries crossed, not elapsed duration
`ROUND()`	Rounds a number to N decimal places
`ISNULL() / COALESCE()`	Replaces NULL with a fallback value
`TRY_CAST() / TRY_CONVERT()`	Converts types, returning NULL instead of erroring on bad input

Practice tip: Write a query that calculates each startup’s funding per employee (funding_usd / employees), formatted to 2 decimal places. Notice you need to think about integer division here too if employees were an INT divided by another INT — it isn’t in this case since funding_usd is DECIMAL, but it’s worth confirming that for yourself by checking the result type.

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

Category: SQL Server Fundamentals

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?

Primary Keys and Foreign Keys in SQL Server: How Tables Actually Connect

The Relationship, Visualized

Watching the Constraint Actually Enforce Something

Three Relationship Shapes

A Many-to-Many Example, Concretely

Enjoyed this?

System vs User-Defined Functions in SQL Server: A First Look

Two Categories

A Preview of What a Scalar UDF Looks Like

Why the Choice Between These Types Actually Matters

Where This Goes Next

Enjoyed this?

SQL Server Built-in Functions: String, Date, and Math Functions You’ll Use Daily

String Functions

Date Functions

Math and Rounding Functions

The Integer Division Trap

NULL-Handling and Conversion Functions

Quick Reference

Enjoyed this?