|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
SQL Server T-SQL User-Defined Function of the Week
Using UDFs in Computed ColumnsVolume 2 Number 1 November 18, 2003
Check out the UDF Frequently Asked Questions at:
CREATE TABLE Products (
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 FUND ( mktfund_seq_num int
CREATE function dbo.udf_Fund_MktCap (
) Returns money -- capitalization of the fund
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)
CREATE TABLE PERSFUND (
[PERSNUM_SEQ_NUM] [bigint] NOT NULL
, [PERSFUND_CAP] AS (dbo.udf_Fund_MktCap(PERSNUM_SEQ_NUM))
INSERT INTO FUND (Mktfund_seq_num, mktfund_cap) VALUES (1, 37.43)
INSERT INTO PERSFUND (persnum_seq_num) VALUES (1) -- no capitalization here.
SELECT * FROM persfund
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:
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.