Using Computed Columns in SQL Server with Persisted Values

By:   |   Comments (26)   |   Related: > Database Design

In my databases I had some values that were often calculated while generating several reports. Also there were some columns that were dependent on one or more other columns. As one column was updated triggers were being used to synchronize new values in dependent columns. I was required to provide a more efficient and standard approach to handle these types of scenarios. So how could I get rid of the overhead for calculations at report generation time and how could I avoid the use of triggers for synchronizing updated columns?

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.

 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.

--Script # 1: Create a table with computed column

USE [AdventureWorks]

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

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

new table

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

computed column

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.

--Script # 2: Insert data in table

USE AdventureWorks
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' 


Here we can see our computed column:

query results

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

--Script # 3: Update DOBirth of empNumb 25

USE AdventureWorks
SET DOBirth = '1960-03-25'
WHERE empnumb = 25

WHERE Empnumb = 25

Here we can see our computed column has been updated.

query results


You may have noticed that we also used the property "Persisted" for our computed column. This property for computed columns was 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 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

  • 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, January 29, 2015 - 1:10:36 AM - Atif Shehzad Back To Top (36096)

Rule is simple. First drop the computed column and then recreate it. In your case an index seems using the column that is preventing the drop. So first generate script of index and drop it. Then you can drop recreate teh column and apply the index back. As an example if i want to change the formula used in this article for reducing it to 10 years then following commands will be used

ALTER TABLE [CCtest]    DROP COLUMN [DORetirement];
ALTER TABLE [CCtest]    ADD [DORetirement] AS (dateadd(year,(10),[DOBirth])-(1)) PERSISTED;

Hopefully that clarifies.


Tuesday, January 27, 2015 - 12:58:45 PM - Abdul Aleem Mohammad Back To Top (36070)

If I want to edit the formula that I am using for a computed column, can I do it suppose if I have a computed column which says multiply with 2 if I want to change the formula to multiply with 4, how can I do it. because when I tried to alter its name or drop it and add a coumns with same name it gave me following error.

Msg 5074, Level 16, State 1, Line 3

The index 'IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints' is dependent on column 'ReportingFpl'.

Msg 4922, Level 16, State 9, Line 3

ALTER TABLE DROP COLUMN ReportingFpl failed because one or more objects access this column.

Msg 2705, Level 16, State 5, Line 1

Column names in each table must be unique. Column name 'ReportingFpl' in table 'dbo.ApplicationData' is specified more than once.

Do you have any idea

Wednesday, January 21, 2015 - 12:28:34 PM - Robert Miller Back To Top (36013)

THANKS! The CASE statement in the comments was golden for my situation with this SQL Tip.

Added a Devault value to the field. Then use this SQL Tip to keep it updated from that point forward.

A CASE statement for 2 other field values that can provide 4 outcomes would be extremely useful.

Friday, September 20, 2013 - 5:49:30 AM - Atif Shehzad Back To Top (26878)

I assume that by term upload you mean to export execl file data to a table. If the table preexists then you have to look for definition of column FarmerID. If it is being computed by some formula then you may be prevented to populate directly. In that case you have to skip the column from export process. If there is some other scenario then please share in more detail.

Thursday, September 19, 2013 - 1:10:35 PM - shepherd Back To Top (26867)

Am trying to upload a excel worksheet into the system but its The column 'FarmerID'cannot be modified beacause it is either a a computed column oris the result of a Union operator.




Tuesday, July 9, 2013 - 4:05:02 AM - Mohammad Shahnawaz Back To Top (25756)

As salamo alaikum wa rahmatullah,

hope you are doing good. i have a problem with following is calculating with all while i need to have different data according to location wise.

Here is Query for that, kindly reply me immediately.


select convert(nvarchar(10),warnt.created_dt,120),loc.loc_name,--loctype.LocationTypeDesc,

(select SUM(stk.sel_price) from tbl_mst_stock as stk 

inner join tbl_gen_warranty as wrn on stk.Stock_ID=wrn.Stock_Id

inner join tbl_mst_model as mdl on mdl.Model_ID=wrn.model_id

inner join tbl_mst_location as lc on lc.Location_ID=wrn.Location_id

inner join tbl_mst_location_types as ltp on ltp.LocationTypeID=lc.LocationTypeID

where wrn.mobile_type like 'SL')'Selleing Price',

(Select COUNT(stk.stk_code) from tbl_mst_stock as stk 

inner join tbl_gen_warranty as warn on stk.Stock_ID=warn.Stock_Id

inner join tbl_mst_model mdl on mdl.Model_ID=warn.model_id

inner join tbl_mst_location as lc on lc.Location_ID=warn.Location_id

inner join tbl_mst_location_types as ltp on ltp.LocationTypeID=lc.LocationTypeID

where warn.mobile_type like 'SL') 'Stock count'

 From tbl_mst_location as loc 

 inner join tbl_mst_location_types loctype on loc.LocationTypeID=loctype.LocationTypeID

 inner join tbl_gen_warranty as warnt on loc.Location_ID=warnt.Location_id

  Where  loctype.LocationTypeID=1 and loctype.group_id=1 

  and (CONVERT(nvarchar(10),warnt.created_dt,120) 

  Between CONVERT(nvarchar(10),'2013-01-01',120) and convert(nvarchar(10),'2013-07-09'))

 Group by loc.loc_name,loctype.LocationTypeDesc,warnt.created_dt



Thanking you


Mohammad Shahnawaz

Oracle's Passion

Wednesday, July 3, 2013 - 4:10:44 AM - Atif Shehzad Back To Top (25687)

Thanks Crickey. We would say that persistance is required when column is imprecise. Other wise as you mentioned that persistance is not mandatory for column that is deterministic and accurate.

Tuesday, July 2, 2013 - 12:59:15 PM - Crickey Back To Top (25679)

Nice article, Atif. Always a pleasure reading your articles. However, I would like to make a correction to one of your statements above viz., "Along with some other conditions Persisted is required to create an index on the computed column".

This is not the case. You can create an Index on a computed column be it persisted or not, as long as it is deterministic and accurate.



Tuesday, May 14, 2013 - 6:00:18 AM - Atif Back To Top (23936)

I am pointing towards data type implicit conversion. This rule is not just sepcific to computed column expression but other scenarios where different data types are used in an expression.

Please refer to following lines of BOL

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned.


Sunday, May 12, 2013 - 1:28:36 PM - Robert Carnegie Back To Top (23909)

I think there's a confusing line where you wrote, "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."

You're talking about data type implicit conversion - such as (columnThatIsChar + columnThatIsInt) attempting to convert and treat both columns as int - but I think you've got it mixed up with precedence of operators like +, *, in which

5 * 2 + 3 * 3 = (5 * 2) + (3 * 3) = 19, whereas (((5 * 2) + 3) * 3)

So do you mean: "If you are combining columns of two different data types in your expression then column of type of lower precedence will be converted to the type of higher precedence."

Tuesday, February 12, 2013 - 3:54:50 AM - Atif Shehzad Back To Top (22057)

It is working without any error. I just added schema name "HumanResources" inside the trigger. Statement became as under

Declare @BillingRate int = (Select BillingRate From HumanResources.Employee E
                                Where E.EmployeeID = @EmployeeID)


Monday, February 11, 2013 - 5:49:27 AM - saeed Back To Top (22045)

Create Schema HumanResources
Create Schema ProjectDetails
Create Table HumanResources.Employee
(EmployeeID int Identity(2,2) Constraint PKemployee Primary key,
FirstName nvarchar(50) not null,
BillingRate money Constraint CH_blling_EMP Check(BillingRate>0))

Create Table ProjectDetails.TimeCards
(TimeCardID int Identity(1,1) Constraint PK_timecard Primary Key,
EmployeeID int Constraint FK_employee_TM Foreign Key References HumanResources.Employee(EmployeeID),
BillableHours int Constraint CH_bill_TM  Check(BillableHours>0),
TotalCost int)
Create Trigger RRG_Total
ON  ProjectDetails.TimeCards
After insert        (or instead of ...)
    Declare @EmployeeID int = (Select EmployeeID From Inserted)
    Declare @BillableHours int = (Select BillableHours From Inserted)
    Declare @BillingRate int = (Select BillingRate From Employee E
                                Where E.EmployeeID = @EmployeeID)
    Update ProjectDetails.TimeCards
    Set ProjectDetails.TimeCards.TotalCost = ( @BillableHours * @BillingRate )


Please run and check this code
Where is the problem?

Thursday, February 7, 2013 - 12:34:49 AM - Atif Shehzad Back To Top (21959)

@Saeed. As i did mention that we may not directly refer the column in to computed column expression from another table. However we may utilize user defined function to indirectly access it in the expression. So first you have to create a UDF that will fetch you billing rate against provided parameter and then use this UDF in computed column expression.



Monday, February 4, 2013 - 5:12:08 PM - saeed Back To Top (21908)

I have a problem

please help

Create Table Payment

( BillableHours int,

TotalCost AS (BillableHours*BillingRate) )

But !

BillingRate belongs to another table and this is not executive

Thursday, January 31, 2013 - 2:04:36 AM - Atif Shehzad Back To Top (21821)

Setting the ANSI_WARNINGS off may affect the determinism of persisted computed column. Quoting an example by BOL

"When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned."

This shows that setting it ON or OFF may result in difference of values or behaviour. However this would not be the case for non persisted computed columns.

So while working with persisted computed columns you have to make sure that SET ANSI_Warnings is ON while creating or manipulting the computed column.

In your case you have to set the option ON to ensure the determinism property of the persisted column.

Wednesday, January 30, 2013 - 4:46:13 PM - Thomas F Back To Top (21805)

If you try to delete from this table with ANSI_WARNINGS OFF, it fails.  Do you know why, or how to work around it?




DELETE FROM CCTest WHERE empnumb = 21

DELETE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Monday, January 21, 2013 - 2:20:15 AM - Atif Shehzad Back To Top (21590)

Hi Daniel, datepart(week,serverdate) is not a deterministic function as required for a persisted computed column. Although other date parts are deterministic like day, month, years etc. But week is affected by firstday of the week setting. It is set through set DATEFIRST. So  datepart(week,serverdate) result would be different for setting of datefirst parameter in the environment. It would make your function non deterministic.


Wednesday, September 26, 2012 - 7:53:35 PM - Daniel Alvarado Back To Top (19698)

Hi, I'm trying to use the function DATEPART (WEEK, serverdate) as a PERSISTED computed column but I get the error

.... cannot be persisted because the column is non deterministic

I need to save the numer of week to use it in a partion scheme.

Could you please help me?

Thanks in advance

Friday, July 13, 2012 - 12:16:08 AM - Atif Back To Top (18484)

@Amar. According to BOL.

Computed column can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators.

A computed column can not be used for another computed column creation. In other words compted column may not be nested. However you may create a view and use the computed column from table to create required computed column in the view. You may also look other ways to avoid dependent computed columns


Thursday, July 12, 2012 - 4:48:04 PM - Amar Back To Top (18483)

How can I make a column computed based on the other computed columns?

For example, if I have a table such as this

Col A (int)

Col b (int)

Col c (computed - col a + col b)

Col d (computed - col c * 0.5)

The SQL server does not let me use col d as computed in this case. Any idea how to fix this? Thank you.

Wednesday, May 9, 2012 - 4:40:08 PM - Jim Back To Top (17385)

I needed to add 


for all my stored procs editing the table with a computed column... (not in the proc, rather during creation) to avoid errors like this:

  [UPDATE failed because the following SET options have incorrect settings: QUOTED_IDENTIFIER] 

create proc dbo.proc_myproc


Wednesday, February 1, 2012 - 11:52:44 PM - Atif Back To Top (15873)

Chiris, Thanks for pointing out this important aspect. I would cover this process and associated concepts in a seperate article.

Wednesday, February 1, 2012 - 3:02:49 PM - Chris Cody Back To Top (15871)
One thing to be aware of, if your computed column derives its value from a function, and you want to change how the function works, you may have to rebuild your table containing the computed column. This means that you should really really like your function before you use it in a computed column.

Friday, March 5, 2010 - 12:15:07 AM - @tif Back To Top (5009)

 Case statement as you used, is very suiatble for this task.


Thursday, March 4, 2010 - 3:46:22 PM - dlsampson Back To Top (5006)

 Wahoo! I figured it out!

You can use CASE functions within the Computed Columns. Here's what worked for me:

(case [ApprovalType] when (2) then dateadd(year,(5),[ApprovalDate]) else dateadd(year,(1),[ApprovalDate]) end)

So, now, all my Conditional Approvals have a 1-year expiry date and all my Full Approvals have a 5-year date.


Thursday, March 4, 2010 - 1:34:42 PM - dlsampson Back To Top (5005)

Excellent Tip! Worked perfectly. Now, I need some help expanding on this. I have a table for Approvals in which I calculate expiration dates for the approval. What I want to do is calculate different expiration dates based upon approval type stored in a single column ApprovalType.

Conditional Approval expires after 1-year

Full Approval expires after 5-years.

Using the Computed Columns in SQL Server tip, how can I calculate dates based upon differing values?


Thanks in advance for your help with this.


get free sql tips
agree to terms