What is Computed Column in sql server

Computed column is a virtual column in sql server, which is not stored physically in the table. It’s data is not materialized in the database and gets calculated during query execution on that column.

It takes it’s value from an expression and that expression can contain other columns to calculate a new value for the computed column.

Can it persist physically in the belonging table?

Yes, and to make it persist physically in table, just mark it as PERSISTED. By default it does not persist physically. In this case the data will be materialized in the table and will not be computed during query execution.

Without physical persistence:

CREATE TABLE TestComputedColumn
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
SALARY NUMERIC(18,2),
TAX NUMERIC(18,2),
NETSALARY AS SALARY - TAX
)

With physical persistence:

CREATE TABLE TestComputedColumn
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
SALARY NUMERIC(18,2),
TAX NUMERIC(18,2),
NETSALARY AS SALARY - TAX PERSISTED
)

Limitations:

1. We can not target this column during insert and update.
2. Default, Foreign key or NOT NULL constraints can not be applied to this column until it’s marked as PERSISTED.

Benefits:

1. Readability : In case of some complex calculation instead of putting all the complex calculations inside SELECT statement at multiple places, we can create some calculated columns without physical persistent to improve the readability.
2. Maintainability : It will also improve the maintainability if we are writing complex expressions various times inside our select query, which is going to be changed frequently. In such type of scenarios, if we are using computed columns, for any future changes we just have to change it at one place (inside table definition) which will increase the maintainability.

To add computed column after table has been created, we can use ALTER TABLE statement as below;

ALTER TABLE TestComputedColumn ADD NETSALARY AS SALARY - TAX

Space calculation for computed columns:-

Look at the below query, its nothing except i am going to insert some hundreds of records into the table. You can use any method to achieve this.

CREATE TABLE TestComputedColumn
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
SALARY NUMERIC(18,2),
TAX NUMERIC(18,2)
)

INSERT INTO TestComputedColumn
(NAME, SALARY, TAX)
SELECT 'Test Name ' + CAST(SEQ AS VARCHAR(5)), SEQ * 1000, (SEQ * 1000) / 10
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS SEQ, * FROM sys.columns) dtDemoTable
WHERE dtDemoTable.SEQ <= 20000

Above, i have created a table named “TestComputedColumn“. And below is the space used by the table before computed column (May be different at your machine from below figure);

SP_SPACEUSED 'TestComputedColumn'

ALTER TABLE TestComputedColumn ADD NETSALARY AS SALARY - TAX

Below is the detail of the space used by table after creating this computed column;

SP_SPACEUSED 'TestComputedColumn'

From above figures you can compare that nothing is changed after creation of computed column without using PERSISTED clause to make it persistent physically.

Drop the existing computed column;

ALTER TABLE TestComputedColumn DROP COLUMN NETSALARY

Now add PERSISTED computed column;

ALTER TABLE TestComputedColumn ADD NETSALARY AS SALARY - TAX PERSISTED

Now again check the space;

SP_SPACEUSED 'TestComputedColumn'

Just see the red circled columns.

The size of the table has been increased now because the data is being stored physically.

Comparison between computed column PERSISTED vs fixed materialized column:

Before proceeding, Just drop the table TestComputedColumn.

DROP TABLE TestComputedColumn
GO

Create the same table with fixed columns (without computed column)

CREATE TABLE TestComputedColumn
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
SALARY NUMERIC(18,2),
TAX NUMERIC(18,2),
NETSALARY NUMERIC(18,2)
)

INSERT INTO TestComputedColumn
(NAME, SALARY, TAX, NETSALARY )
SELECT 'Test Name ' + CAST(SEQ AS VARCHAR(5)), SEQ * 1000, (SEQ * 1000) / 10,

SEQ * 1000 - (SEQ * 1000) / 10 AS NETSALARY

FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS SEQ, * FROM sys.columns) dtDemoTable
WHERE dtDemoTable.SEQ <= 20000

Now update the column NetSalary

UPDATE TestComputedColumn set NETSALARY = SALARY - TAX

Check the space again as;

SP_SPACEUSED 'TestComputedColumn'

This is equal to the computed column with PERSISTED.

Conclusion:

Now from above article we can see that computed columns can increase the readability and save some space in the database only when they are being used without PERSISTED. Instead of creating a computed column with PERSISTED, we should go for fixed regular columns because computed columns has some limitations as discussed above.

Kindly share your comments below.