SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Always update your views

I last posted about changing a table without changing a view that represents the table and how this may affect the view.

In today’s quick post I’d like to show another issue brought about by not maintaining your views.

In case you missed the last post you can find it here.

In that post we created a table named dbo.demo and a view named dbo.vDemoInfo.  Bad practice was used and the view was created with “select *” for demo purposes.

Using the same table, we create a new view without “select *”.

CREATE VIEW [dbo].[vDemoInfo2]
AS
SELECT id, FName, LName, City, State, Zip
FROM demo d with (NOLOCK)
GO

Either highlighting the view name and pressing F1 or running sp_help will show us the details for the view:

image

Now let’s alter the dbo.demo table.

ALTER TABLE dbo.demo ALTER COLUMN Lname nvarchar(50)

Note that we have changed the table to NVARCHAR but at this point the view is still VARCHAR.

image

Summary

Always be sure to update your views when changing the base table.

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.

Comments

Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...