Insulating SQL Server Tables in Views

By: Brent Shaub   |   Updated: 2011-07-18   |   Comments (10)   |   Related: > 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.

About the author

Brent Shaub has been creating, renovating and administering databases since 1998.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips