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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s