Insulating SQL Server Tables in Views

Problem

In a team of SQL and front-end developers, changes to the database can affect team mates inadvertently. Is there a way to establish a base upon which the front-end can be built while allowing necessary database changes to be made? How can impacts to everyone be minimized?  Check out this tip to learn more.

Solution

The use of views can insulate tables from certain kinds of changes, allowing for the front-end to be pinned to stable castles in the sky. This tip will define what I like to call the “Pigs in a Blanket” strategy of using views, how various development scenarios need handled and how to implement them programmatically.

New Development Scenarios

In this approach there is one view per table, typically listing each column and then any one-off columns or combination of columns frequently used. Because each view wraps tightly around its base table, it reminds me of “sausages surrounded by bread”, an hors d’oeuvre called “Pigs in a Blanket”. It’s also similar to insulating copper wires in rubber so there are no unexpected shocks when moving things around.

Consider the table:

create table table1 (

 tbl1_pk int identity(1,1) not null,

 tbl1_val varchar(10) not null,

 tbl1_val2 varchar(10) not null,

 constraint PK_tabl1 primary key (tbl1_pk)

)

Create a table

Each table gets a view that references all of its columns. For useful concatenations or aliases, these are added into the view too.

Rename a table

Using the SP_NAME system stored procedure to change the table name will require altering the view to match the table’s new name. Until the view is altered, using it will result in a binding error.

Msg 208, Level 16, State 1, Procedure v_table1, Line 3

Invalid object name ‘table1_’.

Msg 4413, Level 16, State 1, Line 1

Could not use view or function ‘v_table1’ because of binding errors.

Drop a table

Dropping a table without dropping any referencing views is permitted by SQL Server, but results in binding errors when the view is referenced.

Insert a new column

When adding a table column that needs to be shown in the output, an ALTER VIEW command is needed to append it. If the column is purely for behind-the-scenes work, the view can remain as is.

Rename a column

Perhaps the best use of this technique is that the view can alias a changed column to its original name. No front-end code needs to be changed, but the view will need to be altered.

exec sp_rename

 @objname=’table1.tbl1_val2′,

 @objtype=’column’,

 @newname=’tbl1_val2_’;

 GO

/* SQL Results

Caution: Changing any part of

 an object name could break scripts and stored procedures.

*/

alter view v_table1 as

select

 tbl1_pk,

 tbl1_val,

 tbl1_val2_ as tbl1_val2 — same as before

from table1;

GO

Drop a column

Removing the column from the view is safe way to preserve the data while lessening the result set of queries. If the column is best removed from the table, it will need removed from the view using ALTER VIEW.

Implementation Scripts

This section features some scripts for automating view creation and maintenance.

Wrap all existing tables in Blankets i.e. views

/*

   pre-reqs:  insulate all tables

   limits:   no column reordering

             no value sorting

   post-reqs: handle unique views after

*/

declare curtbl cursor for

select [object_id], [name]

from sys.objects

where [type] = ‘U’

   — optional filter for a specific table

order by [name]

declare @tblid int, @tbl varchar(40),

   @col varchar(40), @comma bit, @aSQL nvarchar(4000)

open curtbl

fetch from curtbl into @tblid, @tbl

while @@fetch_status=0 begin

   set @comma = 0

   declare curcol cursor for

   select [name]

   from sys.columns

   where [object_id] = @tblid

   open curcol

   fetch from curcol into @col

   while @@fetch_status=0 begin

      if @comma = 1 — second time and beyond

         set @aSQL = @aSQL + ‘, ‘

      else — first time through

         set @aSQL = ‘create view v_’+@tbl+’ as select ‘

      set @comma = 1

      set @aSQL = @aSQL + quotename(@col)

      fetch next from curcol into @col

   end

   close curcol

   deallocate curcol
   set @aSQL = @aSQL + ‘ from ‘ + quotename(@tbl)

   print ‘Executing: ‘+ @aSQL

   exec sp_executeSQL @aSQL
   fetch next from curtbl into @tblid, @tbl

end

close curtbl

deallocate curtbl

When creating a new table, you can use the above script and filter on the new table.

One-Off Views

The wrapper script prints the create view statement it will run. This can be useful to quickly implement one-off views using the alter view command.

/* This text was copied from the print statement

 and modified to sew names together */

alter view v_person as

select [pers_id],

 [pers_fname], [pers_lname],

 [pers_lname] + ‘, ‘ + [pers_fname] as [pers_fullname],

 [pers_username], [pers_password],

 [pers_img], [pers_profile]

from [person]

Next Steps

• Discuss with your team the benefits of having wrapper views at the table level.
• Determine a naming convention for tables and views.
• Explore CREATE VIEW and ALTER VIEW in Books Online.
• Review INDEXED VIEWS to see if performance can be improved.
• There will subsequent tips exploring views on a combination of tables.  Stay tuned.
• Check out all of the tips on views.