|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
SQL Server T-SQL User-Defined Function of the Week
Using a UDF and Computed Column to replace a Column with a Lookup.Volume 2 Number 36 September 7, 2004
Check out the UDF Frequently Asked Questions at:
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:http://www.surveymonkey.com/s.asp?u=10004616786
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:
And here is my response:
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: