SQL Server Computed Columns and its uses

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.

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

USEAdventureWorks

GO

CREATETABLETestComputedColumn

(

IDINTNOTNULLIDENTITY(1,1),

FirstNameVARCHAR(50)NOTNULL,

LastNameVARCHAR(50)NOTNULL,

EntryDateDATETIMENOTNULL,

FullNameASFIRSTNAME+' '+LASTNAME,

EntryYearASYEAR(EntryDate),

EntryMonthASMONTH(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.

Transact-SQL

1

2

3

4

5

6

7

8

9

INSERTINTOTestComputedColumn(FirstName,LastName,EntryDate)

SELECT'Devendra','Das',GETDATE()-40

UNIONALL

SELECT'Martin','Hunt',GETDATE()-25

UNIONALL

SELECT'Dev','Sygn',GETDATE()

GO

Select*fromTestComputedColumn

GO

Transact-SQL

1

2

Select*fromTestComputedColumn

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.

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.

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.