Getting creative with Computed Columns in SQL Server

By: Atif Shehzad | Updated: 2011-08-29 | Comments (8) | Related: > TSQL

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.

example2

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.

example3

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

About the author

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

Article Last Updated: 2011-08-29

Wednesday, January 27, 2016 - 6:22:23 PM - Joe Celko	Back To Top (40518)
This is a good article! Declartiave programming is hard for so many people. I have to be a picky, tho. CASE is an expression and not a statement. That is why you can use it in a computation.