Default Values and Computed Columns in SQL Server

Populating and deriving values at the table level

Two table-level features help you keep data consistent without relying on every application to “remember” to fill or calculate values: DEFAULT constraints (SQL Server supplies a value when none is provided) and computed columns (SQL Server derives a value from an expression based on other columns). Used well, they reduce duplicated logic, prevent missing values, and make queries simpler.

1) DEFAULT constraints for common patterns

A DEFAULT constraint defines what value SQL Server should use for a column when an INSERT statement does not provide a value for that column. Defaults are evaluated at insert time (not at query time) and are stored as the inserted value.

Common pattern: created_at timestamps (SYSDATETIME)

A typical pattern is a “created at” timestamp that is always set when the row is created. Using SYSDATETIME() gives a high-precision timestamp from the server.

created_at datetime2(7) NOT NULL CONSTRAINT DF_Table_created_at DEFAULT (SYSDATETIME())

Because the value comes from the database server, it is consistent across applications and services.

Common pattern: default status flags

Status columns often start in a known state, such as “Active”, “Pending”, or a boolean-like flag. Defaults ensure new rows begin with the correct status.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

is_active bit NOT NULL CONSTRAINT DF_Table_is_active DEFAULT (1)

For string statuses, you can default to a literal:

status varchar(20) NOT NULL CONSTRAINT DF_Table_status DEFAULT ('Pending')

Common pattern: default numeric values

Numeric columns such as quantity, discount, or tax rate often have a sensible default (0, 1, or a standard rate). Defaults prevent NULLs from creeping into calculations.

discount_amount decimal(12,2) NOT NULL CONSTRAINT DF_Table_discount_amount DEFAULT (0)

Important behavior: omitted column vs explicitly set to NULL

Defaults apply only when the column is omitted from the INSERT statement (or the keyword DEFAULT is used). If you explicitly insert NULL, SQL Server will attempt to store NULL and the default will not be used.

• Default is used: column not listed in INSERT, or value is DEFAULT.
• Default is not used: column listed with value NULL.

Example patterns:

-- Default applies because created_at is omitted (and is_active is omitted too if it has a default)  INSERT INTO dbo.Orders (customer_id) VALUES (123);  -- Default applies because DEFAULT keyword is used  INSERT INTO dbo.Orders (customer_id, created_at) VALUES (123, DEFAULT);  -- Default does NOT apply; this attempts to store NULL  INSERT INTO dbo.Orders (customer_id, created_at) VALUES (123, NULL);

If the column is defined NOT NULL, inserting NULL will fail. This is often desirable: it forces either omission (so the default applies) or a real explicit value.

2) Computed columns

A computed column is defined by an expression that references other columns in the same row. SQL Server calculates it automatically. This is useful for derived values like totals, normalized formats, or concatenated display fields.

Deterministic expressions

Some expressions always produce the same output for the same input values. These are called deterministic. Determinism matters because it affects whether SQL Server can persist the computed value and whether it can be indexed.

Examples of commonly deterministic expressions include arithmetic (qty * unit_price), string concatenation, and many built-in functions that are deterministic for given inputs. Expressions that depend on “current time” or other changing context are typically non-deterministic and are not good candidates for persisted computed columns.

Persisted vs non-persisted computed columns

Computed columns can be:

• Non-persisted (default): the value is computed when queried. Storage cost is minimal, but repeated computation can add CPU cost for large scans or frequent queries.
• Persisted: SQL Server stores the computed value on disk and maintains it when dependent columns change. This can improve query performance (especially when the computed column is indexed), but increases storage and adds overhead on INSERT/UPDATE.

Definition examples:

-- Non-persisted computed column  line_subtotal AS (quantity * unit_price)  -- Persisted computed column  line_subtotal AS (quantity * unit_price) PERSISTED

Performance trade-off summary:

• Use non-persisted when the expression is cheap and not frequently filtered/sorted/joined on.
• Use persisted when the expression is used often, is expensive, or you want to index it (subject to rules such as determinism and precision).

3) Practical examples tying defaults and computed columns together

Example A: subtotal, tax, total

A common pattern is to store inputs (quantity, unit price, tax rate) and derive outputs (subtotal, tax amount, total). Defaults help ensure calculations don’t break due to missing values, and computed columns keep the math consistent.

• Defaults: quantity defaults to 1, tax_rate defaults to 0 (or a standard rate), created_at defaults to current server time.
• Computed columns: subtotal, tax_amount, total.

When using computed columns for money-like calculations, choose appropriate decimal precision/scale for base columns to avoid rounding surprises.

Example B: normalized formatting columns

Sometimes you want a normalized version of a value for searching or matching (for example, an email lowercased, or a trimmed SKU). A computed column can provide a consistent normalized representation without requiring every application to apply the same formatting rules.

email_normalized AS (LOWER(LTRIM(RTRIM(email)))) PERSISTED

Persisting can be useful if you frequently search by the normalized value and want to index it.

Step-by-step lab: add a DEFAULT, test inserts, then add a computed column

This lab uses a simple table that represents order line items. You will (1) add a DEFAULT constraint to an existing table, (2) insert rows with and without specifying the column, and (3) add computed columns and validate results with SELECT queries.

Lab setup: create a sample table

IF OBJECT_ID('dbo.OrderLine', 'U') IS NOT NULL DROP TABLE dbo.OrderLine;  CREATE TABLE dbo.OrderLine (     order_line_id int IDENTITY(1,1) NOT NULL,     order_id int NOT NULL,     product_id int NOT NULL,     quantity int NOT NULL,     unit_price decimal(12,2) NOT NULL,     tax_rate decimal(5,4) NOT NULL,     created_at datetime2(7) NOT NULL );

Assume this table already exists in your environment; the next steps demonstrate altering an existing table.

Step 1: add DEFAULT constraints to an existing table

Add defaults for created_at and tax_rate. Also add a default for quantity to represent a common “1 item” case.

ALTER TABLE dbo.OrderLine ADD CONSTRAINT DF_OrderLine_created_at DEFAULT (SYSDATETIME()) FOR created_at; ALTER TABLE dbo.OrderLine ADD CONSTRAINT DF_OrderLine_tax_rate DEFAULT (0.0000) FOR tax_rate; ALTER TABLE dbo.OrderLine ADD CONSTRAINT DF_OrderLine_quantity DEFAULT (1) FOR quantity;

These defaults will be used only when the column is omitted (or DEFAULT is specified) during INSERT.

Step 2: insert rows omitting defaulted columns

Insert a row without specifying created_at, tax_rate, and quantity. The defaults should populate them.

INSERT INTO dbo.OrderLine (order_id, product_id, unit_price) VALUES (1001, 2001, 19.99);

Verify what was inserted:

SELECT order_line_id, order_id, product_id, quantity, unit_price, tax_rate, created_at FROM dbo.OrderLine;

Step 3: insert rows specifying explicit values (including DEFAULT)

Insert a row with explicit values, overriding defaults:

INSERT INTO dbo.OrderLine (order_id, product_id, quantity, unit_price, tax_rate, created_at) VALUES (1001, 2002, 3, 5.00, 0.0825, '2026-01-01T10:00:00');

Insert a row using the DEFAULT keyword for one column:

INSERT INTO dbo.OrderLine (order_id, product_id, quantity, unit_price, tax_rate, created_at) VALUES (1001, 2003, 2, 10.00, DEFAULT, DEFAULT);

Now demonstrate that explicitly inserting NULL does not use the default. This statement will fail because tax_rate is NOT NULL:

INSERT INTO dbo.OrderLine (order_id, product_id, quantity, unit_price, tax_rate) VALUES (1001, 2004, 1, 9.99, NULL);

Re-run a SELECT to confirm the successful rows:

SELECT order_line_id, product_id, quantity, unit_price, tax_rate, created_at FROM dbo.OrderLine ORDER BY order_line_id;

Step 4: add computed columns (subtotal, tax_amount, total)

Add computed columns to derive consistent amounts. Here, subtotal is quantity times unit price, tax_amount is subtotal times tax rate, and total is subtotal plus tax.

ALTER TABLE dbo.OrderLine ADD subtotal AS (CONVERT(decimal(12,2), quantity * unit_price)) PERSISTED;  ALTER TABLE dbo.OrderLine ADD tax_amount AS (CONVERT(decimal(12,2), (quantity * unit_price) * tax_rate)) PERSISTED;  ALTER TABLE dbo.OrderLine ADD total AS (CONVERT(decimal(12,2), (quantity * unit_price) * (1 + tax_rate))) PERSISTED;

Notes on the expressions:

• CONVERT(decimal(12,2), ...) forces a consistent scale for stored computed values.
• Persisting these columns trades extra storage and write overhead for faster reads and simpler queries.

Step 5: validate computed results with SELECT queries

Query the table and confirm that computed values match expectations:

SELECT     order_line_id,     product_id,     quantity,     unit_price,     tax_rate,     subtotal,     tax_amount,     total,     created_at FROM dbo.OrderLine ORDER BY order_line_id;

Optionally, compare computed totals to an on-the-fly calculation to validate consistency:

SELECT     order_line_id,     subtotal,     total,     CONVERT(decimal(12,2), (quantity * unit_price) * (1 + tax_rate)) AS total_recomputed FROM dbo.OrderLine ORDER BY order_line_id;

Optional extension: normalized formatting computed column

If you add a column that benefits from normalization (for example, a SKU), you can compute a normalized version for consistent matching.

ALTER TABLE dbo.OrderLine ADD sku varchar(50) NULL; ALTER TABLE dbo.OrderLine ADD sku_normalized AS (UPPER(LTRIM(RTRIM(sku)))) PERSISTED;  SELECT order_line_id, sku, sku_normalized FROM dbo.OrderLine;