Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Insulating SQL Server Tables in Views


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


Implementation Scripts

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.

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.

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]
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.


Last Updated: 2011-07-18


get scripts

next tip button



About the author
MSSQLTips author Brent Shaub Brent Shaub has been creating, renovating and administering databases since 1998.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 27, 2011 - 4:50:05 AM - Maddy Back To Top

It's very good to know how SQL really works. These tables are very helpful and to get more information on SQL that how it works, the commands used and how to make a start, one can check out the following article: http://www.techyv.com/article/what-are-all-flavors-sql-and-differences-between-each

Articles on how SQL works are very helpful as one needs to know that commands to be entered. So please keep sharing articles like these as they are very informative and make things easy for us.


Sunday, July 24, 2011 - 10:39:15 PM - 8080_Diver Back To Top

Frankly, I tend to avoid using views in OLTP settings because they seem to invariably be misused.  As I previously stated, I have a simple rule of thumb for the use of indexes: if there is not a lot of change going on, then a view may be a choice; however, if the data is constantly changing, then a view is not a choice unless it is the last choice. 

I also don't spend a great deal of time perusing BOL . . . I have to get work done. ;-)  While I do spend some time researching new trends and characteristics of databases, a lot of what I know comes from having been working with data design, data manipulations, and data analysis for the last 42 years. ;-)


Saturday, July 23, 2011 - 11:15:31 PM - Brent Shaub Back To Top

Thanks again for your insights, Ralph.  It makes good sense to have a clustered index on a constantly increasing value due to the physical sorting.  I'm thinking twice about how I "save space" when dealing with a many-to-many table with its PK as its two FKs.

Back to this post.  In reading your reply and more in Books Online, it seems that views are best intended for selecting specific data from multiple tables, implementing user-based security separate from the views' tables, sewing partitioned tables together into one or providing backward compatibility (the last touched in the post).  More info about these can be found: Scenarios for Using Views (ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72e5eb37-c21c-49ee-9c53-ad9e1eb6bb5f.htm).  The help page on Types of Views, ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/ba7a317b-7b42-49ea-a1c7-ba8e77daa51f.htm, outlines three main types: standard, indexed and partitioned.  I imagine you're familiar with the contents, and wanted to share with audience where some details may come from (aside from trial, error and sweat :) ).

Again thanks for sharing.  The feedback has been an asset for me, and I hope for the other readers as well.


Friday, July 22, 2011 - 4:37:21 PM - Ralph D. Wilson II Back To Top

A clustered index scan is the same thing as a table scan.  Clustered indexes are physical sorts of the data in the table (which, by the way, is why you really want to have the clustered index on a constantly increasing value such as an Identity column or a DateTime value ;-). 

If you put an index on a view, then you will be able to make use of that index in joining tables/views to that view.  That's the Good News.  The Bad News is that any rows inserted into the table will cause the Indexed View to be rebuilt in its entirety . . . and that means taht, if you should be so brash as to create an indexed view on a frequently updated table in, say, an OLTP database, you can easily wind up with the overhead of the indexed view rebuilds bringing your database to a grinding halt.

If you look back at my original response and read the points _in_ _order_, you will see that I predicated the table scan comment on there NOT being an index on the view and then I explained _why_ I made that assumption.

Indexed views, obvioulsly, have indexes that can be used in joins to the index; normal views do NOT have such indexes. 


Friday, July 22, 2011 - 9:13:10 AM - Brent Shaub Back To Top

Just a note that those ms-help:// links are from Books Online for SQl 2008 Developer Edition.  Cheers.


Friday, July 22, 2011 - 9:11:51 AM - Brent Shaub Back To Top

@8080_Diver, some responses:

Point #1 I've tried to recreate table scanning using SET SHOWPLAN_ALL ON for a table, insulated view of that table, and multiple-table view.  In each step, there is no "table scan" step., usually "clustered index scan" (no where clause) or "clustered index seek".  I'm open to what you're saying, and perhaps I'm going about showing it incorrectly.  Can you outline how you've learnt this?

2.  In joining two views in a query, the results for me was also "clustered index {scan|seek}".  I'm imagining the plan output would state "table scan".  Again, I'm hearing what you're saying, but unsure how to have SQL say the same thing (or perhaps it is, but I'm looking too literally).

Here is an article on Plan Forcing that may be of use:  http://msdn.microsoft.com/en-us/library/ms186343(v=SQL.100).aspx

3.  I've been looking into Books Online, and I believe it depends on the kind of index placed on the view.  A unique, clustered index on a view is claimed to update with each change to the data in the underlying table; an indexed view creates a persistent table in memory unlike a non-indexed view that only gets its executionm plan saved.

View Resolution: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_0evalplan/html/af90cc09-991c-4add-ab19-49d12fd787ed.htm
Designing Indexed Views: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/669b47d9-ab28-4656-ab68-1ed6f69f54c2.htm

I can appreciate the different data-volume scenarios of systems and the importance for performance of getting this correct.  Thank you for your thoughts, and please feel free to continue the discussion (especially if I've misunderstood).


Thursday, July 21, 2011 - 4:25:29 AM - Brent Shaub Back To Top

8080_diver: You clearly know the nuts and bolts of performance better than I do.  Thanks for pointing out these areas, and I will respond to them more intelligently once I've done some research.  As for point number 4, yes, I have some upcoming articles on making Stored Procs to work with Views for CRUD, but they can just as easily refer to tables as well.  Again thanks.


Thursday, July 21, 2011 - 2:18:40 AM - Brent Shaub Back To Top

BPODFW: Yes, that's correct; the example does use the default DBO schema, and your code will allow it to be a named one.  Thank you for this.  Depending on schema names, you might need quotename() in the "@aSQL = create view" line similar to how you reworked the "from" statement near the end.


Tuesday, July 19, 2011 - 3:37:29 PM - BPODFW Back To Top

While it would work for schema of dbo only if you had other named schemas it would fail. The code below will work with named schemas in SQL 2005 or higher

/*
   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'
Select SO.[object_id], SO.[name], SC.[name]
from sys.objects SO
join sys.schemas SC on SC.[schema_id]=SO.[schema_id]
where SO.[type] = 'U'
 -- optional filter for a specific table

order by SO.[name]

declare @tblid int, @tbl varchar(128), @schm varchar(128),
   @col varchar(128), @comma bit, @aSQL nvarchar(4000)
open curtbl
fetch from curtbl into @tblid, @tbl, @schm
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 '[email protected]+'.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(@schm)+'.'+ quotename(@tbl)
   print 'Executing: '+ @aSQL
   exec sp_executeSQL @aSQL
  
   fetch next from curtbl into @tblid, @tbl,@schm
end
close curtbl
deallocate curtbl


Tuesday, July 19, 2011 - 7:15:36 AM - 8080_Diver Back To Top

I am currently dealing with over-zealous View Creations.  When you start to consider using views, there are some things you need to think about, especially with regard to performance:

  1. Views do not usually have any indexes on them.  While the indexes on the various tables may come into play within the view itself, any queries that access the view will tend to follow one of 2 paths, either they will access the entire view or, if you use the view in a JOIN or add a WHERE clause,  they will result in the equivalent of a table-scan on the results of the view.
  2. If you build views using other views, you can bet you are going to have "table-scan" performance issues.
  3. If you do put indexes on a view, you need to bear in mind that any change to the data in the underlying tables will result in the view being rebuilt.  This means that, if your view is in an OLTP environment, you can wind up with an awful lot of overhead from the frequent rebuilding of indexed views.  (Indexed views work best for tables that do not change much, e.g. OLAP tables or static tables used as references.
  4. If you are working with "front end" developers, you may want to consider Stored Procedures instead of views.  If your reason for creating the views is that your front end developers are creating dynamic SQL that is then executed against your database, the problem may be the dynamic SQL rather than the need to protect your data by using views. ;-)

Learn more about SQL Server tools