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:

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.

Summary

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