Computed Columns and UDFs

, 2011-08-30

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

.

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads