Using a UDF and Computed Column to replace a Column with a Lookup.

Welcome back from summer vacation. I hope you all had a good one. I did.

While I was away I spent some time thinking about the future of this newsletter. It was originally conceived as an accompaniment to the book that I was writing, Transact-SQL User-Defined Functions, which was published in October of 2003. There have been 87 issues so far and although I have a lot more UDFs in my library I'm not so sure that they're going to make interesting articles because most are variations on UDFs that you've already seen.

There's another major factor that's going to cause some changes: SQL Server 2005 (Yukon). It's coming, sooner or later. Probably later, but some time in 2005. Syntax changes in T-SQL, changes in UDFs, and inclusion of the .Net runtime in the SQL engine are just three of the major modifications to SQL Server that will change what can be done with T-SQL and User-Defined Functions.

Before I go make changes to the newsletter, I'd like to get some feedback from you, the readers. I've created an on-line survey with just 8 questions that I'd appreciate you filling out at this link:

I'll share the results with you in a few weeks. Now on to this week's UDF.

A few weeks ago reader Rebecca Deacon wrote asking:

I have a table that contains a column. That table and column are used throughout our web site. The column is no longer valid and needs to come from another table. I need to quickly do this without having to change all of the code in the web site. Would altering that table, dropping the invalid column, and adding the column back as a computed column using a function be an option? The function will contain the new table and column.

And here is my response:

Yes, doing what you suggest is an option. However, it may be a very poorly performing option depending on the pattern of access. Each time the column is accessed the lookup must be performed. If you access one row at a time performance won't be any worse than other solutions. If you access a large number of rows at one time performance will be much worse than it currently is. It still may be worth the change.

You might get better performance from renaming the table and creating a view that has a join between the newly named base table and the table that has the other value. Then grant select, insert, update, and delete on the view to the appropriate users. The join is much more efficient than using a UDF in a computed column.

Using a UDF is the way to get a computed column to include data from another table. It's the only way that I know of. Let's create two tables and some data. Since there aren't any reusable UDFs in this issue, you might want to do it in a scratch database.

Create TABLE Lookup1 (Code char(1), CorrespondingValue int)
go

insert into Lookup1 VALUES ('A',  3)
insert into Lookup1 VALUES ('B', 91)
insert into Lookup1 VALUES ('C', 84)
insert into Lookup1 VALUES ('D', 123)
insert into Lookup1 VALUES ('E', 0)
insert into Lookup1 VALUES ('F', 6)
go

CREATE TABLE MyData ([Key] int
                    , Name varchar(64)
                    , Code char(1)
                    , CorrespondingValue int
                    )
go

insert into MyData Values (1, 'Andy', 'D', 1)
insert into MyData Values (2, 'Rebecca', 'D', 3)
insert into MyData Values (3, 'Violet', 'E', 5)
insert into MyData Values (4, 'Eric', 'A', 7)
insert into MyData Values (5, 'Tommy', 'F', 11)
insert into MyData Values (6, 'Christine', 'C', 13)
GO

Let's take a quick look at the data:

Select * from myData
go

(Results)

Key         Name          Code CorrespondingValue 
----------- ------------- ---- ------------------ 
1           Andy          D    1
2           Rebecca       D    3
3           Violet        E    5
4           Eric          A    7
5           Tommy         F    11
6           Christine     C    13

Now what Rebecca wants to do is to remove the CorrespondingValue column from table myData and replace it with a computed column that does a lookup on table LookUp1 and uses the value of CorrespondingValue from that table. The next script does what she asks. First it creates a UDF to do the lookup. Next it drops the old column and adds a computed column that uses the UDF. Here's the script:

Create function dbo.udf_Lookup_CorrespondingValue (

   @Code char(1) -- code to return

) Returns Int
AS BEGIN

    DECLARE @Result int

    SELECT TOP 1 
         @RESULT = CorrespondingValue 
       FROM Lookup1
       WHERE [Code] = @Code

    RETURN @Result

END
GO

GRANT EXEC on dbo.udf_Lookup_CorrespondingValue  to PUBLIC
GO


Alter table myData Drop Column CorrespondingValue
go


ALTER TABLE myDAta 
        Add  CorrespondingValue 
            as dbo.udf_Lookup_CorrespondingValue (Code)
GO

Now let's take a look at myData:

Select * from myData
go

(Results)

Key         Name           Code CorrespondingValue 
----------- -------------- ---- ------------------ 
1           Andy           D    123
2           Rebecca        D    123
3           Violet         E    0
4           Eric           A    3
5           Tommy          F    6
6           Christine      C    84

As you can see, the old values of CorrespondingValue have been replaced with a lookup from the LookUp1 table. Any code in the web site that had read myData.CorrespondingValue will continue to work unchanged. Code that attempted to insert or update myData.CorrespondingValue would fail. I presume from the nature of the original question that there wasn't much, if any, code doing INSERTs and UPDATEs on this column.

As I pointed out there may be a performance issue with using this table but even so it performance penelty may be worth getting the functional change. I'll leave creating the view for next week's issue. You'll also find another discussion of using a UDF in computed columns in Volume 2 Issue #1

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick