SQL Server Computed Columns and its uses

Hi Friends,

There is lot of techniques in SQL Server developer world to make logic as easy to use. Here the discussion about SQL Server computed column basics which make life easy in development when we work into high end logic and migration project.

A computed column is the column where insert or updated data depend on the other column values in the same table and can be come by an expression or filter. We cannot insert explicitly defined value into computed column. We can say computed column worked as a virtual column and can be use this as pre calculated value. Computed Column Expression defined as combination of other Non-Computed column, variables, constant and function in the same table. It cannot use be a sub query, query processor can’t use output columns as input columns. So in other word it is not possible to reference the column alias defined at the same level.

This would be helpful:

• For reporting purpose when reports have lot of calculation then we can use this for pre calculation.
• When index exceeds the maximum length of 900 bytes then we can create computed column then apply index.
• When need to create index as function based index as ORACLE.
• Would be helpful for query performance when its use with user defined function.

Following script to create the sample table with computed column definition.

USE AdventureWorks
GO
CREATE TABLE TestComputedColumn
(
ID INT NOT NULL IDENTITY (1, 1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
EntryDate DATETIME NOT NULL,
FullName AS FIRSTNAME+' '+LASTNAME,
EntryYear AS YEAR(EntryDate),
EntryMonth AS MONTH(EntryDate)
) ON [PRIMARY]
GO

Here 3 column FullName, EntryYear, EntryMonth is the computed column. It’s worked as a virtual column as there is no data stored on the disk and value calculated on the fly when called by TSQL script. Now INSERT some value into test table with 3 rows inserting with different date.

INSERT INTO TestComputedColumn(FirstName,LastName,EntryDate)
SELECT 'Devendra','Das',GETDATE()-40
UNION ALL
SELECT 'Martin','Hunt',GETDATE()-25
UNION ALL
SELECT 'Dev','Sygn',GETDATE()
GO
Select * from TestComputedColumn
GO

Select * from TestComputedColumn
GO

We can see FullName, EntryYear, EntryMonth is showing values as per defined expression. For FullName we only combined FisrtName and LastName, for EntryYear we used function YEAR(EntryDate) and for EntryMonth used MONTH(EntryDate). All 3 values calculated every time when referenced in the TSQL.

Persisted Computed Column

If we want to save data into disk to achieve fast result then we can define computed column as PERSISTED. Once column mark PERSISTED then it’s computed right away and stored into data table. We can use computed column with CHECK Constraints, Foreign Key and NOT NULL Constraints if it’s persisted. With every use of INSERT or UPDATE command computed column data updated and auto synchronize with other Non-Computed column. We can define Persisted column with use PERSISTED property on CREATE or ALTER command.

This would be helpful:

• Performance can be improved when use persisted column and building an index on it.
• Persisted column cost is lower that Non-persisted column into SELECT query plan.

ALTER TABLE TestComputedColumn add EntryDay AS DAY(EntryDate) PERSISTED;
GO
Select * from TestComputedColumn
GO

Here you see EntryDay column generated value for DAY(EntryDate) expression. We can create CHECK Constraints with EntryDay computed column but cannot create CHECK Constraints for EntryMonth computed column because it’s not persisted.

ALTER TABLE TestComputedColumn ADD CHECK (EntryDay<32)
GO
Command(s) completed successfully.
	 
ALTER TABLE TestComputedColumn ADD CHECK (EntryMonth<13)
GO
 
Msg 1764, Level 16, State 1, Line 1
Computed Column 'EntryMonth' in table 'TestComputedColumn' is invalid for use in 'CHECK CONSTRAINT' because it is not persisted.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

For Primary OR Unique Key Constraints computed column must be defined by deterministic expression. SQL Server defined some built-in deterministic and nondeterministic functions. We cannot create Primary Key OR Unique Key Constraints on nondeterministic functions used in computed column expression. We cannot create PERSISTED computed column with nondeterministic function. Now we are adding another computed column with nondeterministic function RAND().

ALTER TABLE TestComputedColumn add RandomValue AS RAND() PERSISTED;
GO
Msg 4936, Level 16, State 1, Line 1
Computed column 'RandomValue' in table 'TestComputedColumn' cannot be persisted because the column is non-deterministic.
 
ALTER TABLE TestComputedColumn add RandomValue AS RAND();
GO
 
Command(s) completed successfully.

Here we see the error to create persisted computed column with nondeterministic function RAND() but when PERSISTED property removed then computed column created successfully. We can see the values through select data command. Now try to create NONCLUSTERED INDEX on computed column which used nondeterministic function RAND() but we can create INDEX on deterministic function like DAY.

CREATE NONCLUSTERED INDEX [TestComputedColumn_Unique] ON TestComputedColumn
(
   RandomValue ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 
GO
	 
Msg 2729, Level 16, State 1, Line 2
Column 'RandomValue' in table 'TestComputedColumn' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
 
CREATE NONCLUSTERED INDEX [TestComputedColumn_Unique] ON TestComputedColumn
(
   EntryDay ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 
GO
 
Command(s) completed successfully.

Here you can see an error message about non-deterministic field when try to create INDEX.

Computed column can be used at where clause and ORDER by clause but neither used as DEFAULT and FOREIGN KEY constraints nor with NOT NULL constraints. If we want to EDIT computed column definition then it must be DROP and re-create again.

Regards

Neeraj Yadav

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook