Tim Barcz

SQL Server 2000, watch your views when you update a table

I ran into an interesting gotcha yesterday.  To summarize, when you change a table in SQL Server 2000, if a view is looking at that table, you need to update the view.

Here is an excerpt from SQL Server Books Online:

If a view depends on a table (or view) that was dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created, and the table structure does not change from the previous base table, to replace the one dropped, the view again becomes usable. If the new table (or view) structure changes, then the view must be dropped and recreated.

What this means is that is that if you have a view that reads:

CREATE VIEW dbo.SampleView
AS
SELECT     *
FROM         dbo.Users

If you then added a column to the Users table, that table will not be shows if you use SampleView.  You must first drop the view and then recreate it.

Hope this tip helps someone out there.