Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using Computed Columns in SQL Server with Persisted Values

MSSQLTips author Atif Shehzad By:   |   Read Comments (23)   |   Related Tips: More > 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?

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.

Script # 1: Create a table with computed column
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.

Script # 2: Insert data in table

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.

Script # 3: Update DOBirth of 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


Last Update: 2/6/2009


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, March 04, 2010 - 1:34:42 PM - dlsampson Read The Tip

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.

David


Thursday, March 04, 2010 - 3:46:22 PM - dlsampson Read The Tip

 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.

David


Friday, March 05, 2010 - 12:15:07 AM - @tif Read The Tip

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

Thanks


Wednesday, February 01, 2012 - 3:02:49 PM - Chris Cody Read The Tip
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.

Wednesday, February 01, 2012 - 11:52:44 PM - Atif Read The Tip

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


Wednesday, May 09, 2012 - 4:40:08 PM - Jim Read The Tip

I needed to add 


SET ANSI_NULLS, QUOTED_IDENTIFIER ON

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] 

SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
create proc dbo.proc_myproc

...


Thursday, July 12, 2012 - 4:48:04 PM - Amar Read The Tip

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.


Friday, July 13, 2012 - 12:16:08 AM - Atif Read The Tip

@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

Thanks


Wednesday, September 26, 2012 - 7:53:35 PM - Daniel Alvarado Read The Tip

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


Monday, January 21, 2013 - 2:20:15 AM - Atif Shehzad Read The Tip

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.

Thanks


Wednesday, January 30, 2013 - 4:46:13 PM - Thomas F Read The Tip

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

Thanks

 

SET ANSI_WARNINGS OFF
GO

DELETE FROM CCTest WHERE empnumb = 21
GO

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.


Thursday, January 31, 2013 - 2:04:36 AM - Atif Shehzad Read The Tip

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.


Monday, February 04, 2013 - 5:12:08 PM - saeed Read The Tip

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, February 07, 2013 - 12:34:49 AM - Atif Shehzad Read The Tip

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

Thanks

 


Monday, February 11, 2013 - 5:49:27 AM - saeed Read The Tip

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 ...)
AS
    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?


Tuesday, February 12, 2013 - 3:54:50 AM - Atif Shehzad Read The Tip

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)

 


Sunday, May 12, 2013 - 1:28:36 PM - Robert Carnegie Read The Tip

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, May 14, 2013 - 6:00:18 AM - Atif Read The Tip

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.


Thanks


Tuesday, July 02, 2013 - 12:59:15 PM - Crickey Read The Tip

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.

 

 


Wednesday, July 03, 2013 - 4:10:44 AM - Atif Shehzad Read The Tip

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 09, 2013 - 4:05:02 AM - Mohammad Shahnawaz Read The Tip

As salamo alaikum wa rahmatullah,

hope you are doing good. i have a problem with following query.it 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


Thursday, September 19, 2013 - 1:10:35 PM - shepherd Read The Tip

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.

WHAT SHOULD I CHECK FOR

 

 


Friday, September 20, 2013 - 5:49:30 AM - Atif Shehzad Read The Tip

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.



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
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.