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 withWITH 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 RIGHTputs 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?
Log in to save your progress.
Sources
- Primary and foreign key constraints, Microsoft Learn
- Unique and check constraints, Microsoft Learn
- Sequence numbers, Microsoft Learn
- Partitioned tables and indexes, Microsoft Learn