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:

• Table valued (or defined) UDFs
• Scalar UDFs
• CLR 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.

CREATE TABLE points
( USERID int
, ItemID int
, points tinyint
)
GO
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
CREATE FUNCTION UDF_GetUserPoints
( @UserID int
) RETURNS int
AS
BEGIN
 DECLARE @sum INT
 SELECT @sum = SUM( points)
   FROM Points
   WHERE UserID = @UserID
RETURN @Sum
END

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'
UNION
SELECT dbo.UDF_GetUserPoints(2) AS 'points'
------------------------*/points
-----------
4
5

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)

CREATE TABLE UserProfile
( UserID INT
, UserName VARCHAR(200)
, points AS dbo.UDF_GetUserPoints(USerID)
)
go
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.

SELECT TOP 10  UserID ,
        UserName ,
        points
 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