Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

The Voice of the DBA


Computed Columns and UDFs

I wrote about the basics of computed columns and also using CASE in a computed column recently, but there’s a better way to implement a computation, and reuse the code. You can include a UDF in a computed column.

UDFs are a great way to encapsulate your code into an object that can be included in stored procedures or even computed columns. The basic definition of a UDF is that it’s a function (as in other languages), but it’s designed to be included in other code, unlike a stored procedure. They’re very similar, but there are a couple types of UDFs:

* The CLR UDFs can be scalar or table valued.

For computed columns, you can use a UDF as well. Let me set up a couple tables here and a function. First I’ll set up a table similar to the one on SQLServerCentral that holds user points, add some data, and then create a quick function that calculates the sum of a user’s points.

( USERID int
, ItemID int
, points tinyint
INSERT INTO points SELECT 1, 1, 2
INSERT INTO points SELECT 1, 2, 1
INSERT INTO points SELECT 1, 3, 1
INSERT INTO points SELECT 2, 1, 1
INSERT INTO points SELECT 2, 2, 1
INSERT INTO points SELECT 2, 3, 2
INSERT INTO points SELECT 2, 4, 1

( @UserID int

 SELECT @sum = SUM( points)
   FROM Points
   WHERE UserID = @UserID


If I run the function by itself, I can get the sum of each user’s point total.

SELECT dbo.UDF_GetUserPoints(1) AS 'points'
SELECT dbo.UDF_GetUserPoints(2) AS 'points'

Now let’s go back and set up the user table. I could alter this table if it existed, but in this case I’ll add the points as well as a calculated value for the user’s points. As long as I’m not asking for lots of user’s from this table, this technique is probably OK. Otherwise, I might have a big performance issue. (no SELECT *s from this table)

( UserID INT
, UserName VARCHAR(200)
, points AS dbo.UDF_GetUserPoints(USerID)
INSERT INTO userprofile SELECT 1, 'Steve'
INSERT INTO dbo.UserProfile SELECT 2, 'Andy'

Note that I’m not adding a value for the points column. This is a computed column, so I ignore it in inserts.

        UserName ,
 FROM dbo.UserProfile

UserID      UserName      points
1           Steve         4
2           Andy          5

Here the values are calculated from the other table, pulled from my UDF in the computed column.

Not necessarily a great technique, and I would be careful about using this. Since this function is non-deterministic, we can’t persist the values in the table, so this means that any access of this table for the points column would result in the function execution for the row. Potentially a performance issue.

If you want to see this idea in action, there’s a similar video on UDFs in Computed Columns at SQL Share as well that covers the topic.

Disclosure: I am a part owner in SQL Share


Filed under: Blog Tagged: sql server, syndicated, T-SQL


No comments.

Leave a Comment

Please register or log in to leave a comment.