By: Atif Shehzad | 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.
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
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
