Getting creative with Computed Columns in SQL Server

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