Create an Updatable View

As I mentioned last issue I spent some time thinking about the future of this newsletter over my summer vacation. With SQL Server 2005 on the way there will be a lot of changes in how to program SQL Server and I think they should be reflected in the newsletter.

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:

There have already been a few dozen responses and many helpful comments. I'll share the results with you in a few weeks. Now on to this week's UDF.

We'll not exactly a UDF but a continuation of the discussion started last week about what to do in one difficult situation. Reader Rebecca Deacon wrote asking if she could use a UDF in a computed column to replace an existing column. The reason for the replacement is that the column must now be looked up from a separate table. You can re-read the last issue at this link:

My response was that, yes, you could use a UDF in this situation but there were potential performance problems. One possible alternative solution is to use an updatable view in place of the table. What I'm going to do is a quick rehash of the UDF solution and then show how to transform that into an updatable view.

Here's the script to create the example from last issue. It starts by creating and populating the Lookup1 table and the myData table that represent the original situation...

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

Now create the UDF udf_Lookup_CorrespondingValue to be used in the computed column.

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

Next we replace the column CorrespondingValue with a computed column of the same name that uses the UDF to get it's value from the Lookup1 table.

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

Instead of storing CorrespondingValue in myData it's now retrieved from LookUp1. This achieves Rebecca's original objective.

The alternative solution using a view is next. It starts by renaming the myData table to myDataBaseTable, and dropping the CorrespondingValue column.

sp_rename 'myData', 'myDataBaseTable'
go

ALTER TABLE myDataBaseTable DROP COLUMN CorrespondingValue
go

Now a view is created named myData that replaces the myData table and that can be used by the client side code that references the table.

create view myDAta as
 select m.*, Lookup1.CorrespondingValue
    FROM MyDataBaseTable m
       LEFT OUTER JOIN Lookup1
          on m.Code = LookUp1.Code
GO

GRANT ALL ON myData to PUBLIC
go

Take a look at the data. It's the same as with the UDF.

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

The myData view is updatable. It's got to be for this solution to work without modifying the original application. Here's an example of code that updates myData.

UPDATE myData SET [Name] = 'Andy N' WHERE [Key]  =1

SELECT * from myData WHERE [Key] = 1
SELECT * from myDataBaseTable WHERE [Key] = 1
GO

(Results)

Key         Name            Code CorrespondingValue 
----------- --------------- ---- ------------------ 
1           Andy N          D    123


Key         Name            Code 
----------- --------------- ---- 
1           Andy N          D

So by updating the myData view the correct row in myDataBaseTable was changed. This is pretty cool. Existing client side code continues to work. But the CorrespondingValue column now comes from table LookUp1.

The advantage of this solution over the UDF based solution is performance in situations where a large number of rows are retrieved at once. When a UDF is used in a computed column, the UDF must be executed once for each row retrieved. When the join in the view is executed, SQL Server can use it's much faster mechanisms for processing the join.

Next issue explores a complication that crops up with situation. It turns out that the CorrespondingValue column is updatable. What happens when you execute this code?

UPDATE myDAta SET CorrespondingValue = 37 WHERE [Key] = 1
GO

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