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














































Getting creative with Computed Columns in SQL Server

MSSQLTips author Atif Shehzad By:   |   Read Comments (5)   |   Related Tips: More > T-SQL
Problem

I have used computed columns in simple scenarios and these are working fine. However in some cases we are facing limitations while implementing business logic through computed columns. For example we are required to have different values based on a separate expression to determine the computed values. Also, there maybe the chance of a divide by zero error which needs to be prevented. We are also required to access columns outside the computed column table for use in the computed column expression.  In this tip we look at how to make computed columns more flexible.

Solution

Computed columns can add great flexibility in database design. It is possible to conditionally apply the computational expression, handling divided by zero error and accessing any column outside the table of computed column. This would make the computed column more flexible and handy for your database design. Following is list of some scenarios that we are going to handle in this tip.

  • Conditional computation of values
  • Divided by zero error
  • Accessing a column outside the computed column table

We will use simple T-SQL techniques to solve the above mentioned issues. This will demonstrate the flexibility and ability of computed columns to handle any such issues.


Conditional Computation of Values

We have a simple table with some columns related to employees. There is also a computed column [DORetirement] which computes retirement at age 60.  We have a new requirement to set the age limit to 65 for managers, but keep it at 60 for all other employees.

We can accomplish this by using a conditional formula using a CASE statement as shown in the following code.  For the DORetirement column if the designation is "Manager" then the retirement will be 65 else it will be 60.

-- Script# 1: Computed column with conditional formula
-- Use sample database
USE [AdventureWorks]
GO 
 
-- Create Table with computed column
IF OBJECT_ID('CCtest', 'U') IS NOT NULL
  DROP TABLE [dbo].[CCtest]
GO
 
CREATE TABLE [dbo].[CCtest]
  (
  [EmpNumb] INT NOT NULL,
  [Designation] VARCHAR(50) NOT NULL,
  [DOBirth] DATETIME NOT NULL,
  [DORetirement] AS 
    CASE WHEN designation = 'Manager' 
      THEN (DATEADD(YEAR,(65),[DOBirth]))
      ELSE (DATEADD(YEAR,(60),[DOBirth]))
    END
)
GO
 
--Insert sample data 
INSERT INTO CCTest (empNumb, Designation, DOBirth) 
SELECT 84, 'DBA', '1985-12-13' UNION ALL
SELECT 85, 'DBA', '1980-11-18' UNION ALL
SELECT 86, 'Manager', '1978-01-19' UNION ALL
SELECT 88, 'Manager', '1985-12-13' UNION ALL
SELECT 90, 'Developer', '1975-07-23' 
GO
 
-- Check the required functionality in result
SELECT Designation, datediff(yy,dobirth,doretirement ) AgeLimit, DOBirth, DORetirement 
FROM CCTest
GO

If you run the above code you should get output like the following.  By using a CASE statement, we can define separate expressions depending on the provided criteria.

Conditional formula in computed column


Divided by Zero Error

In computational expression, if we are dividing then there may be a chance to have a zero in the denominator. In such cases we run the risk of having an error because of a divide by zero.

Consider a scenario where we have a computed column with formula [Result] AS (numerator/denominator) where numerator and denominator columns are used for the computed column [DivideResult].  In this case a divide by zero error would be encountered whenever the computed column expression is calculated with a zero in the denominator column.

We can avoid this error by replacing the zero in denominator with a NULL value using the NULLIF function as shown below.  The fourth record we insert into the table would cause a divide by zero issue, but the NULLIF function converts this to a NULL.  

-- Script# 2: Avoiding divided by zero error
-- Use sample database
USE [AdventureWorks]
GO 
-- Create Table with computed column
IF OBJECT_ID('CCtest', 'U') IS NOT NULL
  DROP TABLE CCtest
GO
CREATE TABLE [dbo].[CCtest]
  (
  [Numerator] int NOT NULL,
  [Denominator] int NOT NULL,
  [Result] AS (Numerator/NULLIF(Denominator,0)) 
  )
GO
--Insert sample data
INSERT INTO CCTest (Numerator, Denominator) 
SELECT 840, 12 UNION ALL
SELECT 805, 6 UNION ALL
SELECT 846, 3 UNION ALL
SELECT 88, 0 UNION ALL
SELECT 90, 15
GO
-- Check the result
SELECT * from CCTest
GO

Since you can not divide by NULL the returned value is NULL for this column for this one record.

By default a computed column will allow NULL values. However you can explicitly specify NOT NULL with persisted computed columns only.


Accessing a column outside of the computed column table

A computed column can not directly access any column outside its table. This limitation may be overcome by using a User Defined Function. A UDF may be used in the expression to access any column outside the computed column table.

In the script below, a UDF is created to calculate the employee leave balance to show how many remaining days of leave an employee has beyond the maximum of 20 days.  This data comes from a secondary table named LeaveBalance.

--Script # 3: Use UDF to access column in other table
-- Use sample database
USE [AdventureWorks]
GO 
-- Create Table to reference in UDF
IF OBJECT_ID('LeaveBalance', 'U') IS NOT NULL
  DROP TABLE LeaveBalance
GO
CREATE TABLE [dbo].[LeaveBalance]
  (
  [EmpNumb] INT NOT NULL,
  [LeavesAvailed] TINYINT NOT NULL,
  )
GO
--Insert sample data
INSERT INTO LeaveBalance
SELECT 840, 12 UNION ALL
SELECT 805, 6 UNION ALL
SELECT 846, 13 UNION ALL
SELECT 88, 7 UNION ALL
SELECT 90, 15
GO
-- Create UDF to get leave balance
IF OBJECT_ID('UDF_GetLeaveBalance', 'FN') IS NOT NULL
  DROP FUNCTION UDF_GetLeaveBalance
GO
-- Create UDF to use in computed column
CREATE FUNCTION UDF_GetLeaveBalance (@EmpNumb int)
RETURNS TINYINT
AS
BEGIN
  DECLARE @LeaveBalance TINYINT
  SELECT @LeaveBalance = (20 - LeavesAvailed) 
  FROM LeaveBalance
  WHERE EmpNumb = @empnumb
  RETURN @LeaveBalance
END
GO
-- Create Table to use computed column
IF OBJECT_ID('CCTest', 'U') IS NOT NULL
  DROP TABLE CCtest
GO
CREATE TABLE [dbo].[CCtest]
  (
  [EmpNumb] INT NOT NULL,
  [Designation] VARCHAR(50) NOT NULL,
  [LeaveBalance] AS ([dbo].UDF_GetLeaveBalance(EmpNumb))
  )
GO
--Insert sample data
INSERT INTO CCTest (EmpNumb, Designation) 
SELECT 840, 'DBA' UNION ALL
SELECT 805, 'DBA' UNION ALL
SELECT 846, 'Manager' UNION ALL
SELECT 88, 'Manager' UNION ALL
SELECT 90, 'Developer' 
GO
-- Check the result
SELECT * from CCTest
GO

So below we can see the number of remaing days available for each employee.

There are some considerations related to UDFs when a computed column is to be used in an index.  You can read more about this in this tip: How to create indexes on computed columns in SQL Server.

Next Steps

The above mentioned problems and their solutions work in the same way for persisted or non persisted computed columns. Utilizing the available flexibility of computed columns may improve your database design. Computed columns are a good option to use to implement business logic through expressions.

  • Click here to read tip about basics of working with computed columns
  • Click here to read tip about creating indexes on computed columns


Last Update: 8/29/2011


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




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, July 31, 2013 - 12:26:37 PM - Jacobus Read The Tip

Would it be more performat to write functions that access different tables as table valued functions instead of scalar valued functions?

 


Thursday, August 01, 2013 - 4:31:54 AM - Atif Shehzad Read The Tip

It will not be a performance based decision but depends upon required functionality.

 

Thanks


Friday, October 25, 2013 - 7:20:29 AM - Talitha Read The Tip

Sorry if this is the wrong place to post/ask you this. I'm using SQL Server 2005 and was using this post as a guide but I want to use 2 conditions for my column. Here is my problem:

What I want in the column BehindOrOnSchedule is to display whether a part is 'On Schedule' or 'Behind Schedule'.

If Completed contains 'N' (as in No) and StageDueDate is smaller than today's date, it is behind schedule. Otherwise the part will still be on schedule.

My coding so far:

CREATE TABLE [dbo].[tblParts_ManStages](

[Parts_ManStages_ID][int]IDENTITY(1,1)NOTNULL,[Part_ID_FK][int]NOTNULL,[ManStage_ID_FK][int]NOTNULL,[Completed][nchar](1)NOTNULL,[StageDueDate][nchar](50)NOTNULL,[BehindOrOnSchedule]ASCASEWHEN Completed ='N'AND StageDueDate < GETDATE()THEN'Behind Schedule'ELSE'On Schedule'END

I saved it like this but it is not working. Could you please have a look and tell me what I'm doing wrong?

Thank you (very much) in advance!


Thursday, November 14, 2013 - 4:48:18 PM - Sean Ed Read The Tip

Here's one, and forgive me I'm a bit of a noob.

I'm finding in 2008 R2 formulas aren't working in the following situations, let me know if I'm incorrect:

 - Formulas will not work if mixing arguments.  For example, you couldn't add two column and subtract a third in the same  column.

 - Formulas will not work for more than 5 columns.  For example you could add 5 separate columns, but not 6.

 - Formulas cannot calculate based off of other calculated fields

 

Any thoughts or am I stuck?


Saturday, November 16, 2013 - 12:44:32 AM - Atif Read The Tip

Formulas will work fine with mix arguments/operators. Also work fine for more than five columns. However calculated column formula can not contain another calculated column in it.

If you have any issue with calculated column then please post the code definition for that.

Thanks



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

Signup for our newsletter


Comments
*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 | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.