Launch Deal · 50% off 1st month ·

1.4 — Constraints, Sequences and Partitioning

DP-800 Domain 1 · Skill 1.4

What You Need to Know

Constraints keep data correct, sequences generate numbers in a controlled way, and partitioning keeps very large tables manageable.

Constraints

Constraint Enforces Example
PRIMARY KEY Unique, not-null row identity (creates a clustered index by default) CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderID)
FOREIGN KEY Referential integrity; optional ON DELETE CASCADE or SET NULL FOREIGN KEY (CustomerID) REFERENCES dbo.Customer(CustomerID)
UNIQUE No duplicates in a column set (one NULL allowed) UNIQUE (Email)
CHECK A rule for each row CHECK (Quantity > 0 AND Discount BETWEEN 0 AND 0.5)
DEFAULT The value used when an insert supplies none DEFAULT (SYSUTCDATETIME()) FOR CreatedAt
  • Constraints are enforced by the engine for every client, so they're more reliable than application checks.
  • Index the columns that foreign keys use, which speeds up joins and cascading checks.
  • Watch out for WITH NOCHECK: an untrusted constraint isn't used by the optimizer. Re-enable it with WITH CHECK CHECK CONSTRAINT.

Sequences versus IDENTITY

CREATE SEQUENCE dbo.InvoiceNumber AS bigint START WITH 100000 INCREMENT BY 1 CACHE 50;
DECLARE @next bigint = NEXT VALUE FOR dbo.InvoiceNumber;   -- available before the insert
Need IDENTITY SEQUENCE
A simple auto-number for one table ✅ ✅
One number series shared by several tables ❌ ✅
Get the value before inserting (for example to use it in two tables) ❌ (SCOPE_IDENTITY() only after) ✅ NEXT VALUE FOR
Reserve a range in advance, restart or cycle Limited ✅ sp_sequence_get_range, RESTART, CYCLE

Both can leave gaps (rollbacks, caching, restarts). Never rely on them for gap-free numbering such as legal invoice series without extra logic.

Partitioning tables and indexes

CREATE PARTITION FUNCTION pf_month (date) AS RANGE RIGHT
    FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01');
CREATE PARTITION SCHEME ps_month AS PARTITION pf_month ALL TO ([PRIMARY]);
CREATE TABLE dbo.Sales (...) ON ps_month (SaleDate);
Benefit How
Partition elimination Queries that filter on the partitioning column touch only the relevant partitions
Fast loading and archiving ALTER TABLE ... SWITCH PARTITION moves a whole partition in as a metadata-only operation; TRUNCATE TABLE ... WITH (PARTITIONS (n)) removes one
Per-partition maintenance Rebuild or compress only the active partitions
Aligned indexes Indexes partitioned the same way as the table, which switching requires
  • Partition by a column used in most filters and for data lifecycle, usually a date.
  • RANGE RIGHT puts each boundary value in the partition to its right. That's the usual choice for dates.
  • Partitioning is mainly a manageability feature. It doesn't automatically speed up queries that don't filter on the partitioning column.

Key Concept: Put rules in constraints. Use a sequence when several tables share numbering or you need the value before inserting. Partition large tables by date so that you can eliminate partitions and switch them in and out.

Exam Traps

  • Using IDENTITY when two tables must draw from the same number series
  • Expecting gap-free values from IDENTITY or SEQUENCE
  • Partitioning by a column that queries never filter on
  • Switching partitions with non-aligned indexes

Check your understanding

Practice Scenario

Invoices and credit notes are stored in two different tables, but they must share one continuous document number series. The application needs the number before it inserts the row, so it can print it on a PDF. What should you use?

Report issue

Log in to save your progress.

Sources