Computed Columns and Multi-Column CHECK Constraints in SQL Server

CHECK constraints get genuinely powerful once they can reference multiple columns in the same row — exactly the boundary the previous lesson drew — and computed columns let you derive values automatically instead of trusting every INSERT/UPDATE to calculate them correctly and consistently.

A Table Using Both

CREATE TABLE dbo.Booking (
    booking_id  INT IDENTITY(1,1) PRIMARY KEY,
    check_in    DATE NOT NULL,
    check_out   DATE NOT NULL,
    nightly_rate DECIMAL(8,2) NOT NULL CHECK (nightly_rate > 0),
    nights      AS DATEDIFF(DAY, check_in, check_out),
    total_cost  AS (DATEDIFF(DAY, check_in, check_out) * nightly_rate) PERSISTED,
    CONSTRAINT CK_Booking_Dates CHECK (check_out > check_in)
);

INSERT INTO dbo.Booking (check_in, check_out, nightly_rate) VALUES ('2024-06-01', '2024-06-05', 150.00);
SELECT booking_id, nights, total_cost FROM dbo.Booking;

Notice nights and total_cost are never written to directly — you can’t INSERT a value into them, and SQL Server rejects the attempt if you try. They’re recalculated automatically from check_in, check_out, and nightly_rate every time those source columns change, which structurally guarantees they can never drift out of sync the way a manually-maintained “total” column could.

PERSISTED: Store It or Compute It Live?

Worthwhile when the column is read often relative to how often source columns change — total_cost on a booking is a good candidate, since it’s likely queried far more often than a booking’s dates ever change after creation. A table-level CHECK constraint (like CK_Booking_Dates) can reference multiple columns in the same row — just never another table or another row, which was exactly the boundary the previous lesson established.

A Deterministic Requirement, Revisited

-- FAILS: a computed column referencing GETDATE() cannot be PERSISTED or indexed,
-- for the exact same non-deterministic reason a scalar function couldn't be (Chapter 2)
ALTER TABLE dbo.Booking ADD days_until_checkin AS DATEDIFF(DAY, GETDATE(), check_in) PERSISTED;
-- Msg 4936: Computed column 'days_until_checkin' in table 'Booking' cannot be persisted
-- because the column is non-deterministic.