Insulating SQL Server Tables in Views
By: Brent Shaub | Updated: 2011-07-18 | Comments (10) | Related: More > Views
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.
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 used in subsequent examples
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.
Binding Error that happens with SP_RENAME and DROP TABLE or DROP COLUMN
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.
Rename a column and modify the view example
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.
This section features some scripts for automating view creation and maintenance.
Wrap all existing tables in Blankets i.e. views
"Pigs in a Blanket" View Creation Script
/* 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_'[email protected]+' 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.
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.
Alter View copied from print statements
/* 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]
- 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.
Last Updated: 2011-07-18
About the author
View all my tips