A computed column in SQL Server is a table column whose value is calculated from an expression involving other columns in the same table.

Unlike a normal column, an application usually does not insert a value directly into a computed column. SQL Server calculates the value automatically.

For example, a Product table may contain:

ProductName
CategoryName
SearchColumn

The SearchColumn column may be calculated using:
ProductName + ' - ' + CategoryName

Copy

Search Site

Search Google

This can be useful for searching, sorting, displaying combined values, or indexing a reusable expression.

[SQK Server - Find a Computed Column by Name]

Find the Table Containing the Computed Column
Use the following query to find a computed column by name:

CFFCS | CarrzSynEdit: | SQL Script

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    cc.definition AS ComputedDefinition,
    cc.is_persisted AS IsPersisted
FROM sys.computed_columns AS cc
INNER JOIN sys.columns AS c
    ON cc.object_id = c.object_id
   AND cc.column_id = c.column_id
INNER JOIN sys.tables AS t
    ON cc.object_id = t.object_id
WHERE c.name = 'SearchColumn';

[The results provide the information needed to recreate the column:]

SchemaName
TableName
ColumnName
ComputedDefinition
IsPersisted

[A sample result may look like:]

SchemaName: dbo
TableName: Products
ColumnName: SearchColumn
ComputedDefinition: ([ProductName] + ' - ' + [CategoryName])
IsPersisted: 1
Understanding the Computed Definition

Definition Column

The definition column contains the SQL expression used to calculate the value.
For example:

([ProductName] + ' - ' + [CategoryName])

Copy

Search Site

Search Google
The expression may also use SQL Server functions:
CONCAT([ProductName], ' - ', [CategoryName])

Copy

Search Site

Search Google

[SQK Server - Conditional Logic]

It may include conditional logic:

CFFCS | CarrzSynEdit: | SQL Script

CASE
    WHEN [CategoryName] IS NULL THEN [ProductName]
    ELSE [ProductName] + ' - ' + [CategoryName]

END

When moving the computed column to another database, the destination table must contain all columns and functions referenced by the expression.

Non-Persisted Computed Column

[SQK Server - ALTER TABLE]

A non-persisted computed column is calculated when SQL Server reads or uses it.
Use the following syntax:

CFFCS | CarrzSynEdit: | SQL Script

ALTER TABLE dbo.Products
ADD SearchColumn AS
(
    [ProductName] + ' - ' + [CategoryName]

);

[SQK Server - Calculates]

No value is manually inserted into SearchColumn.
SQL Server calculates it automatically:

CFFCS | CarrzSynEdit: | SQL Script

SELECT
    ProductName,
    CategoryName,
    SearchColumn
FROM dbo.Products;

A non-persisted computed column generally does not store the calculated result as a normal table value.

This can reduce storage usage, but SQL Server may need to calculate the expression each time it is referenced.

Add a Persisted Computed Column

[SQK Server - PERSISTED ]

A persisted computed column stores the calculated result physically in the table.
Use the PERSISTED keyword:

CFFCS | CarrzSynEdit: | SQL Script

ALTER TABLE dbo.Products
ADD SearchColumn AS
(
    [ProductName] + ' - ' + [CategoryName]

) PERSISTED;

SQL Server calculates and stores the value whenever one of the source columns changes.

For example, if ProductName or CategoryName is updated, SQL Server recalculates SearchColumn.

Persisted Versus Non-Persisted

[SQK Server - Non-PERSISTED]

A non-persisted computed column is calculated as needed:

CFFCS | CarrzSynEdit: | SQL Script

ALTER TABLE dbo.Products
ADD SearchColumn AS
(
    [ProductName] + ' - ' + [CategoryName]

);

[SQK Server - PERSISTED]

A persisted computed column stores the calculated result:

CFFCS | CarrzSynEdit: | SQL Script

ALTER TABLE dbo.Products
ADD SearchColumn AS
(
    [ProductName] + ' - ' + [CategoryName]

) PERSISTED;

The main differences are:

[Non-Persisted]

Calculated when referenced
Usually uses less table storage
May require repeated calculation
Suitable for simple expressions

[Persisted]

Calculated and physically stored
Uses additional table storage
Can improve performance for repeated searches
May be indexed when SQL Server requirements are met
Automatically updates when source values change
When to Use PERSISTED

[A persisted computed column may be useful when:]

The expression is used frequently.
The calculation is expensive.
The column is used for searching or sorting.
The column needs an index.
The stored value improves query performance.

For example, a search procedure may repeatedly use:
WHERE SearchColumn LIKE '%' + @SearchText + '%'

Copy

Search Site

Search Google

Persisting

Copy

Search Site

Search Google

the value may avoid recalculating a complicated expression during every search.
However, performance should be tested. Persisting every computed column is not automatically better because it increases storage and adds work during inserts and updates.

[Non-Persisted Computed Column]

When to Use a Non-Persisted Column
A non-persisted computed column may be better when:

The expression is simple.
The column is not queried frequently.
Storage usage is more important than calculation cost.
The source columns rarely appear in search conditions.
The computed value does not require an index.

A simple concatenation may not require persistence unless it is heavily used.

[SQK Server - ALTER TABLE statement]

Generate the ALTER TABLE Script Automatically
The following query generates the appropriate ALTER TABLE statement using the existing computed-column definition:

CFFCS | CarrzSynEdit: | SQL Script

SELECT
    'ALTER TABLE '

    + QUOTENAME(SCHEMA_NAME(t.schema_id))
    + '.'

    + QUOTENAME(t.name)
    + ' ADD '

    + QUOTENAME(c.name)
    + ' AS '

    + cc.definition
    + CASE
        WHEN cc.is_persisted = 1 THEN ' PERSISTED;'

        ELSE ';'

      END AS AddComputedColumnScript
FROM sys.computed_columns AS cc
INNER JOIN sys.columns AS c
    ON cc.object_id = c.object_id
   AND cc.column_id = c.column_id
INNER JOIN sys.tables AS t
    ON cc.object_id = t.object_id
WHERE c.name = 'SearchColumn';

[SQK Server - Generate Code]

Run this query in the database that already contains the computed column.
It will generate a statement similar to:

CFFCS | CarrzSynEdit: | SQL Script

ALTER TABLE [dbo].[Products]
ADD [SearchColumn] AS
([ProductName] + ' - ' + [CategoryName]) PERSISTED;

Copy the generated statement and run it against the destination database.

[SQK Server - Confirm column does not exist.]

Check Whether the Column Already Exists
Before adding the column, confirm that it is not already present:

CFFCS | CarrzSynEdit: | SQL Script

IF COL_LENGTH('dbo.Products', 'SearchColumn') IS NULL

BEGIN
    ALTER TABLE dbo.Products
    ADD SearchColumn AS
    (
        [ProductName] + ' - ' + [CategoryName]

    ) PERSISTED;
END;

This prevents an error if the script is accidentally executed more than once.

[SQK Server - Verify its definition]

Verify the Computed Column
After adding the column, verify its definition:

CFFCS | CarrzSynEdit: | SQL Script

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    cc.definition AS ComputedDefinition,
    cc.is_persisted AS IsPersisted
FROM sys.computed_columns AS cc
INNER JOIN sys.columns AS c
    ON cc.object_id = c.object_id
   AND cc.column_id = c.column_id
INNER JOIN sys.tables AS t
    ON cc.object_id = t.object_id
WHERE t.name = 'Products'

  AND c.name = 'SearchColumn';

[SQK Server - Test its Values]

You can then test its values:

CFFCS | CarrzSynEdit: | SQL Script

SELECT TOP 20
    ProductName,
    CategoryName,
    SearchColumn
FROM dbo.Products;
Important Migration Considerations

Before copying a computed column to another database, verify:

The destination table exists.
The destination table has the required source columns.
The source column data types are compatible.
Any functions used by the expression also exist.
The computed column does not already exist.
The persisted setting matches the original database.
Any related indexes are scripted separately.

Adding the computed column does not automatically recreate an index associated with it. Indexes, constraints, and dependent stored procedures should also be compared between the databases.

Computed columns are useful for creating reusable search values without requiring applications to manually maintain duplicate data. By retrieving the original expression from sys.computed_columns

Copy

Search Site

Search Google

, you can accurately recreate the column and preserve whether SQL Server stores the result using PERSISTED