Computed columns, persisted computed columns, and indexed views

Overview

Computed columns and indexed views are SQL Server features for defining reusable derived data in the database. A computed column derives a value from other columns in the same row. A persisted computed column stores that derived value physically. An indexed view stores the result of a schema-bound view by creating a unique clustered index on the view.

These features are useful when a repeated calculation, normalized search key, derived business value, or aggregate query is important enough to be modeled and optimized in the database. They can make queries simpler and faster, but they also add write overhead and come with strict rules.

This topic matters because interviewers often use it to test whether a candidate understands the difference between logical convenience and physical optimization. A computed column may just be a reusable expression. A persisted computed column trades storage and write cost for read performance and indexing options. An indexed view can precompute expensive joins or aggregations, but it has many restrictions and must be maintained during base-table changes.

For interviews, strong candidates can explain when these features help, when they become unnecessary complexity, and what requirements SQL Server enforces around determinism, precision, schema binding, SET options, and DML maintenance cost.

Core Concepts

Computed Columns

A computed column is a column whose value is calculated from an expression instead of being directly inserted by the application.

Example:

CREATE TABLE dbo.OrderLine
(
    OrderLineId BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(19, 4) NOT NULL,
    DiscountAmount DECIMAL(19, 4) NOT NULL,
    LineTotal AS ((Quantity * UnitPrice) - DiscountAmount)
);

LineTotal is computed from other columns in the same row. The application does not insert it directly.

Benefits:

• Avoids repeating formulas in many queries.
• Keeps business calculations consistent.
• Makes query code easier to read.
• Can sometimes be indexed if requirements are met.
• Reduces application/database mismatch for derived values.

Limitations:

• The expression must be based on allowed deterministic logic.
• It cannot depend on rows from other tables.
• It is not a replacement for all business logic.
• Complex expressions can make writes and reads harder to reason about.

Non-Persisted Computed Columns

By default, a computed column is not physically stored. SQL Server calculates it when needed.

Example:

CREATE TABLE dbo.Customer
(
    CustomerId BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    FullName AS (FirstName + N' ' + LastName)
);

FullName is convenient for queries, but it does not store a separate copy of the full name unless the column is persisted or indexed in a way that stores values.

Use non-persisted computed columns when:

• The calculation is cheap.
• The value is mostly for readability.
• The expression is not heavily filtered or joined.
• You do not need to store the result physically.

Persisted Computed Columns

A persisted computed column stores the calculated value in the table and updates it when dependent columns change.

Example:

CREATE TABLE dbo.Customer
(
    CustomerId BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Email NVARCHAR(320) NOT NULL,
    NormalizedEmail AS (UPPER(Email)) PERSISTED
);

CREATE UNIQUE INDEX UX_Customer_NormalizedEmail
ON dbo.Customer (NormalizedEmail);

Persisting can help when the expression is expensive, frequently queried, or needs to be indexed. It trades storage and write cost for read efficiency.

Important point: persisted does not mean manually maintained. SQL Server maintains the value as dependent columns change.

Use persisted computed columns when:

• The expression is deterministic.
• The value is frequently filtered, joined, or indexed.
• Recomputing the value repeatedly is expensive.
• The storage and write overhead are acceptable.
• You need SQL Server to store the computed result.

Determinism And Precision

Computed columns must meet requirements before they can be indexed. Two major concepts are determinism and precision.

A deterministic expression always returns the same result for the same input values.

Deterministic example:

Total AS (Quantity * UnitPrice)

Nondeterministic example:

CreatedAgeSeconds AS (DATEDIFF(second, CreatedAt, SYSUTCDATETIME()))

The second expression changes as time passes, even when the row values do not change.

Precision matters when floating-point types are involved. Expressions involving FLOAT or REAL can be imprecise and may not be allowed as index keys.

Interview rule: if you want to index a computed column, expect SQL Server to care about ownership, determinism, precision, data type, and session SET options.

Indexes On Computed Columns

Computed columns can be indexed when SQL Server's requirements are satisfied.

Example:

CREATE TABLE dbo.Users
(
    UserId BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Email NVARCHAR(320) NOT NULL,
    NormalizedEmail AS (UPPER(Email)) PERSISTED
);

CREATE UNIQUE INDEX UX_Users_NormalizedEmail
ON dbo.Users (NormalizedEmail);

This supports case-normalized uniqueness and lookup patterns.

Example query:

SELECT UserId, Email
FROM dbo.Users
WHERE NormalizedEmail = UPPER(@Email);

The computed-column index can avoid recomputing the expression for every row.

Requirements can include:

• Same owner for referenced functions and the table.
• Deterministic expression.
• Precise expression for index keys.
• Supported data type.
• Required SET options.
• QUOTED_IDENTIFIER and ANSI_NULLS expectations at creation time.

SARGability And Computed Columns

Computed columns are often used to make expression-based searches more indexable.

Problem pattern:

SELECT UserId, Email
FROM dbo.Users
WHERE UPPER(Email) = UPPER(@Email);

Applying a function to the column can prevent efficient use of a normal index on Email.

Computed column pattern:

ALTER TABLE dbo.Users
ADD NormalizedEmail AS (UPPER(Email)) PERSISTED;

CREATE INDEX IX_Users_NormalizedEmail
ON dbo.Users (NormalizedEmail);

SELECT UserId, Email
FROM dbo.Users
WHERE NormalizedEmail = UPPER(@Email);

This turns a repeated expression into a modeled, indexable value. It is especially useful for normalized search keys, date buckets, trimmed codes, and business calculations used in predicates.

Indexed Views

An indexed view is a view with a unique clustered index. Creating that index stores the view result set in the database similarly to a table with a clustered index.

Example:

CREATE VIEW dbo.vDailyProductSales
WITH SCHEMABINDING
AS
SELECT
    o.OrderDate,
    ol.ProductId,
    COUNT_BIG(*) AS RowCount,
    SUM(ol.Quantity) AS TotalQuantity,
    SUM(ol.Quantity * ol.UnitPrice) AS TotalRevenue
FROM dbo.Orders AS o
JOIN dbo.OrderLines AS ol
    ON ol.OrderId = o.OrderId
GROUP BY
    o.OrderDate,
    ol.ProductId;
GO

CREATE UNIQUE CLUSTERED INDEX CX_vDailyProductSales
ON dbo.vDailyProductSales (OrderDate, ProductId);

This can speed up repeated aggregation queries. Instead of recalculating daily product sales from base tables every time, SQL Server maintains the indexed view as base data changes.

Indexed View Requirements

Indexed views have stricter rules than ordinary views.

Important requirements include:

• The view must use WITH SCHEMABINDING.
• The first index on the view must be a unique clustered index.
• Referenced tables must use two-part names such as dbo.Orders.
• The view expression must be deterministic.
• The base table and view ownership chain must be compatible.
• Required SET options must be correct.
• Aggregated indexed views need COUNT_BIG(*).
• Many constructs are not allowed, including SELECT *, TOP, DISTINCT, UNION, outer joins, subqueries, common table expressions, window functions, and some aggregate forms.

These restrictions exist because SQL Server must maintain the indexed view reliably as base rows change.

Computed Columns Vs Indexed Views

Computed columns and indexed views solve different problems.

Use a computed column when:

• The derived value comes from columns in the same row.
• You want to reuse or index a row-level expression.
• The value belongs naturally to the table.
• The query filters or joins on an expression of that row.

Use an indexed view when:

• The derived result spans multiple rows or tables.
• You need precomputed aggregation.
• You need a materialized projection of joined data.
• The read benefit outweighs write overhead and restrictions.

Example distinction:

-- Row-level derived value
LineTotal AS ((Quantity * UnitPrice) - DiscountAmount)

This belongs as a computed column.

-- Multi-row aggregate by day and product
SUM(Quantity * UnitPrice)
GROUP BY OrderDate, ProductId

This is a better fit for an indexed view or reporting table.

Write Overhead And Maintenance Cost

Persisted computed columns and indexed views speed up some reads by doing more work during writes.

When base data changes, SQL Server must maintain:

• Persisted computed column values.
• Indexes on computed columns.
• Indexed view rows.
• Nonclustered indexes on indexed views.

This can slow INSERT, UPDATE, and DELETE operations. It can also increase locking, logging, storage, and deployment complexity.

Interview answer: do not add persisted computed columns or indexed views just because they are clever. Add them when a measured read workload benefits enough to justify the write cost.

SET Options And Operational Surprises

SQL Server requires specific session SET options for indexed computed columns and indexed views. If connection settings are wrong, index creation may fail or the optimizer may ignore the indexed structure for query plans.

Important options include:

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER
• NUMERIC_ROUNDABORT

The usual pattern is that most must be ON, while NUMERIC_ROUNDABORT must be OFF.

This is why indexed views and computed-column indexes should be created through controlled migrations, not ad hoc manual scripts with unknown connection settings.

Indexed Views Vs Reporting Tables

An indexed view is maintained synchronously by SQL Server as base tables change. A reporting table is usually maintained by a job, ETL process, trigger, queue, or application workflow.

Indexed view advantages:

• Automatically maintained.
• Query optimizer may use it.
• Good for stable, deterministic aggregations.
• Lives inside the relational schema.

Reporting table advantages:

• More flexible transformation logic.
• Can tolerate eventual consistency.
• Can include data from multiple databases or services.
• Easier to customize for analytics workloads.
• Avoids some indexed view restrictions.

Choose based on freshness needs, write overhead, query frequency, complexity, and operational ownership.

Common Mistakes

Common mistakes include:

• Using GETDATE() or other nondeterministic functions in computed columns intended for indexing.
• Expecting computed columns to pull data from other tables.
• Persisting every computed column without measuring write overhead.
• Forgetting that indexed views require WITH SCHEMABINDING.
• Forgetting COUNT_BIG(*) in grouped indexed views.
• Using unsupported syntax such as SELECT *, UNION, TOP, CTEs, or outer joins in indexed view definitions.
• Ignoring required SET options.
• Creating indexed views on highly volatile tables without testing DML cost.
• Using indexed views as a substitute for a proper reporting model.

Best Practices

Best practices:

• Use non-persisted computed columns for cheap row-level convenience.
• Use persisted computed columns for expensive or indexed row-level expressions.
• Use computed-column indexes for normalized search keys and repeated predicates.
• Use indexed views only when read performance clearly justifies write overhead.
• Keep computed expressions deterministic and precise.
• Use schema-bound, explicit, two-part object names in indexed views.
• Benchmark with realistic read and write workloads.
• Include SET options in migrations.
• Prefer simpler indexes or query rewrites before adding complex materialized structures.
• Document why the derived structure exists and what query pattern it supports.