N S

Novick Software Management • Design • Programming • Training • Consulting

   

 

 

SQL Server T-SQL User-Defined Function of the Week

Using UDFs in Computed Columns

Volume 2 Number 1   November 18, 2003

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.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick


RSS as HTML

Personal Blog
 
New Tips:

Loading SQL Profiler trace (.trc) files with fn_trace_gettrace
Upcoming
Presentations:

Big Data:
Working with Terabytes
in SQL Server
July 9 '08

Nov 19-21
SQL Pass 2008

Full Schedule