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.
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
.
