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