Using UDFs in Computed Columns

Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Transact-SQL User-Defined Functions has been published!  Take a look at it right now!

Computed columns are a feature of SQL Server that allows you to move business logic into the database. The typical computed column is a formula that operates on two or more columns from the same table. Here's an example:

     CREATE TABLE Products (
            ProductID int
          , QuantityOnHand int
          , UnitPrice money
          , InventoryValue = QuantityOnHand * UnitPrice
      )

 

InventoryValue is the computed column. SQL Server doesn't store its value, as you might if you managed it with a trigger. Instead, SQL Server computes the value each time it's read.

 

That's an important distinction because of the storage vs. performance tradeoff. Had the InventoryValue column been created as a money column and its value set in both the INSERT and UPDATE triggers, two things would have been different: - The InventoryValue column would be stored in every row - SQL Server wouldn't have to compute it's value each time the row is read.

In the grand scheme of things, there will probably be more CPU time saved by not reading extra pages than could ever be saved by non performing the multiplication. Using the computed column is usually a good bet.

However, unlike a trigger, a computed column can't reference data in another table. That's a big limitation that will usually force a decision in favor of using the trigger.

User-defined functions (UDFs) can be the answer. A user defined function can reference columns in another table and the UDF can be invoked in a computed column. Put these together and you have a potential answer to the problem.

I wrote the following script as response to someone who posted a question on Experts Exchange. The question asked if it were possible to use a SELECT in the formula for a computed column. The answer is: No! it's not. But you can use a UDF that contains the select.

The table definition that the question posed was:

CREATE TABLE [dbo.][PERSFUND] (
      [PERSNUM_SEQ_NUM] [bigint] NOT NULL ,
      [PERSFUND_CAP] AS (SELECT mktfund_cap
                            FROM FUND
                            WHERE mktfund_seq_num =
                                               persfund_seq_num
                       )
     )
GO

Notice the SELECT in the definition of PERSFUND_CAP. It doesn't work. When you try and create that table, you'll get an error something like "Error validating the formula for column PERSFUND_CAP."  Of course, if you don't have the FUND table in your database, you may get some other error message. The following script creates the tables and implements the computed column. I suggest that you create it in a scratch database. Here's the script with the UDF:

CREATE TABLE FUND ( mktfund_seq_num int
                    ,mktfund_cap money
                  )
Go
 
CREATE function dbo.udf_Fund_MktCap (
    @Fund_seq_num int
) Returns money -- capitalization of the fund
AS BEGIN
 
    DECLARE @mkt_cap money
 
    SELECT TOP 1
          @Mkt_Cap = mktfund_cap
       FROM FUND WHERE mktfund_seq_num = @Fund_seq_num 
    RETURN COALESCE (@Mkt_Cap, 0.00)
END
go
 
CREATE TABLE PERSFUND (
       [PERSNUM_SEQ_NUM] [bigint] NOT NULL
     , [PERSFUND_CAP] AS (dbo.udf_Fund_MktCap(PERSNUM_SEQ_NUM))
)
GO
 
INSERT INTO FUND (Mktfund_seq_num, mktfund_cap) VALUES (1, 37.43)
go
 
INSERT INTO PERSFUND (persnum_seq_num) VALUES (1) -- no capitalization here.
go
 
SELECT * FROM persfund
go

(Results)

PERSNUM_SEQ_NUM      PERSFUND_CAP
-------------------- ---------------------
1               37.4300

And there you have it.  The PERSFUNC_CAP column returns the market capitalization that is stored in the FUND table.

This is all well and good, but is it the right way to go?  There are several alternatives to achieving the same functionality. The ones that I can thing of are:

• Use a column and have triggers on the FUND table update it.
• Use a computed column and a UDF as shown in the script above.
• Use a view that joins the two tables.

There are tradeoffs in performance and maintainability no matter which alternative that you choose.

If the frequency of SELECTs on the PERSFUND table is high relative to the frequency of updates, the trigger might be the most efficient. However, if the frequency of updates is high enough, the view is probably the most efficient, followed by the UDF, but I couldn't say for sure.