Using Computed Columns in SQL Server with Persisted Values

By: Atif Shehzad | Read Comments (6) | Print Atif is a passionate SQL Server DBA, technical reviewer and article author. Related Tips: More

Solution
For such scenarios where calculated values are required or values are generated through manipulation on other columns, we have a powerful feature provided in SQL Server. This feature is "Computed Columns".

A computed column is computed from an expression that can use another column or columns in the same table. Functions, variables, constants, non computed column names or any combination of all these may be used along with operators to create a computed column. In this tip we will go through an example of implementing a computed column.

I have tested the scripts on SQL Server 2005 and SQL Server 2008. In the following script we will create a table called CCtest in the AdventureWorks database with three columns [empNumb], [DOBirth] , [DORetirement].

We are required to have the "Date of Retirement" for each employee as (DOBirth + 60 years - 1 day). Instead of calculating it each time in the report or updating the column [DORetirement] each time through a trigger when [DOBirth] is updated, we have a better approach here to create [DORetirement] as a computed column.  Since this rule could change at anytime we are implementing it as a computed column instead of a hard coded value.

USE [AdventureWorks]
GO 
-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO

The same may be done through SSMS. Create new table by right clicking on the Tables folder in the AdventureWorks database.

You will be provided a design view for the new table in SSMS. Provide specifications for the calculated column as shown below.

Now we have our table CCtest with a computed column. Similarly we can add a computed column to any existing table using the "ALTER TABLE" command or opening the table in design view using SSMS and making the changes.

Let's insert some data and run a query to test the functionality of the computed column.

USE AdventureWorks
GO 
INSERT INTO CCTest (empNumb, DOBirth)
SELECT 30 ,'1985-12-13' UNION ALL
SELECT 25 ,'1980-11-18' UNION ALL
SELECT 21 ,'1978-01-19' UNION ALL
SELECT 7 ,'1985-12-13' UNION ALL
SELECT 5 ,'1975-07-23' 
GO
SELECT * FROM dbo.CCTest
GO

Here we can see our computed column:

To verify that the computed column will be updated for any updates, we will update [DOBirth] for [empNumb] 25.

USE AdventureWorks
GO 
UPDATE CCtest
SET DOBirth = '1960-03-25'
WHERE empnumb = 25
GO
SELECT * FROM dbo.CCTest
WHERE Empnumb = 25
GO

Here we can see our computed column has been updated.

Persisted
You may have noticed that we also used the property "Persisted" for our computed column. This property for computed columns has been introduced in SQL Server 2005 and onwards. It is important for any computed column, because many additional features depend on it. To be able to make a computed column as Persisted it has to be deterministic.

Here are a few rules:

• If Persisted property is off then calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk.
• Any update in referenced column will be synchronized automatically in computed column if it is Persisted.
• Along with some other conditions Persisted is required to create an index on the computed column.

Nullability
Nullibility for a computed column value will be determined by the database engine itself. The result of a non-nullable referenced column may be NULL in certain conditions to avoid possible overflows or underflows. You can provide an alternate value for NULL using the ISNULL(check_expression, constant), if required.

Some Limitations

• For SQL Server 2000 you can not create a persisted computed column.
• You can not reference columns from other tables for a computed column expression directly.
• You can not apply insert or update statements on computed columns.
• If you are combining operators of two different data types in your expression then operator of lower precedence will be converted to that of higher precedence. If implicit conversion is not possible then error will be generated.
• A subquery can not be used as an expression for creating a computed column.
• Computed columns can be used in SELECT lists, WHERE or ORDER BY clauses and as regular expressions , but to use a computed column as CHECK, FOREIGN KEY or NOT NULL constraints you have to set it to Persisted.
• To use a computed column as Primary or Unique Key constraint it should be defined by a deterministic expression and data type of computed column expression should be indexable.

Next Steps

• While planning for any computed column, please keep in mind that although a Persisted computed column will reduce overhead for calculations at run time it will consume more space on disk.
• To get Nullability of any computed column in a table, use the COLUMNPROPERTY function with the AllowsNull property.
• Creating indexes on computed columns requires certain conditions to be fulfilled. For details of these conditions please visit BOL

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code