Creating Persisted Computed Columns using Scalar Valued Functions

Computed columns are a really handy feature in SQL Server which allow you to calculate a column based on the value in one or more other columns. By default, a particular value for computed column is calculated on the fly every time the row is accessed.

There is a computational overhead incurred by the calculation, and in some cases it is better to persist the computed value to increase performance. This will depend on a few factors, including the complexity of the calculation, and the ratio of data modification to selects being issued against the table.

Which brings me to the topic of this post: Using a Scalar Valued Function to build a persisted computed column.

The following script shows the basic steps required to create a table that includes a computed column

--Create a test table
CREATE TABLE T1
(
 ID      INT                      NOT NULL,
 Price   DECIMAL(9,2) NOT NULL,
 Qty     INT                      NOT NULL,
 Total AS (Price * Qty)
) 

--Add Test Data
INSERT T1(ID, Price, Qty) VALUES (1,10.50,2) 

--Check Results
SELECT * FROM T1
 

This works well for basic calculations. What if we want to do something more complicated? The next step is to replace the calculation above with a call to a user defined scalar function. Here is a really simple function to demonstrate the point:

CREATE FUNCTION fnCalculateTotal( @price DECIMAL(9,2), @qty INT)
    RETURNS INT
AS
BEGIN
    RETURN @price * @qty
END 

Next step: Modify the table to use the function instead of the hard-coded calculation.

--Create a test table
CREATE TABLE T1
(
 ID      INT                      NOT NULL,
 Price   DECIMAL(9,2) NOT NULL,
 Qty     INT                      NOT NULL,
 Total AS dbo.fnCalculateTotal(Price, Qty)
) 
 

So far so good. But now I want to change the Total column to be persisted, so I don’t have to incur the overhead of calculating the value every time I query the table. To achieve this, add the PERSISTED keyword to the column definition:

--Create a test table
CREATE TABLE T1
(
 ID      INT                      NOT NULL,
 Price   DECIMAL(9,2) NOT NULL,
 Qty     INT                      NOT NULL,
 Total AS dbo.fnCalculateTotal(Price, Qty) PERSISTED
) 

Msg 4936, Level 16, State 1, Line 2
Computed column 'Total' in table 'T1' cannot be persisted because the column is non-deterministic.

Non Determinism. Hmmm

A function is described as being derterministic if it always returns the same value when called with the same input parameters. The function above is deterministic, but SQL Server doesn’t know that. An example of a nondeterministic function is:

GETDATE()

Getdate is nondeterministic because it gives a different result every time it is called, despite the input parameters being the same.

To tell SQL Server that our function is deterministic, we need to add the WITH SCHEMABINDING keyword to the definition

CREATE FUNCTION fnCalculateTotal( @price DECIMAL(9,2), @qty INT)
    RETURNS INT
WITH SCHEMABINDING    
AS
BEGIN
    RETURN @price * @qty
END 

SQL Server will now allow the creation of the persisted computed column in T1, as the function is guaranteed to return reliable results. In my next post, I'll discuss using a persisted computed column in conjunction with table partitioning.