A reader, Rebecca, 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.
If your access pattern includes reading multiple rows, 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.
goinsert 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)
goCREATE 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:
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 its performance penalty may be worth getting the functional change. I’ll leave creating the view for another column coming up soon. You’ll also find another discussion of using a UDF in computed columns.