Getting creative with Computed Columns in SQL Server
By: Atif Shehzad | Updated: 2011-08-29 | Comments (8) | Related: More > TSQL
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.
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.
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
View all my tips
Article Last Updated: 2011-08-29