Avoid schema changes corrupting your views

Maybe you are using views as a layer between your tables and your users. If you have a lot of views you properly know the case where a table is change and now your view is not working anymore.

Lets see how to avoid schema changes corrupting your views.

First an example showing the problem. Start creating a table named t2 and a view named v2

CREATE TABLE t2 (RowID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(100))
GO

CREATE VIEW v2 AS (SELECT RowId, Name FROM t2)
GO

SELECT * FROM v2
GO

Then change the table, and select from the view.

ALTER TABLE t2 DROP COLUMN Name
GO

SELECT * FROM v2
GO

You will see this error. If you have users on your system. Running queries depending on your view your system is not running anymore.  

Lets see what you should have done.

Lets start drop the view and table

DROP TABLE t2
GO

DROP VIEW v2
GO

Now we will recreate the table and view. The view will be created with the WITH SCHEMABINDING option

CREATE TABLE t2 (RowID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(100))
GO

CREATE VIEW v2 WITH SCHEMABINDING AS (SELECT RowId, Name FROM dbo.t2)
GO

ALTER TABLE t2 DROP COLUMN Name
GO

When we are trying to change the t2 table we will see this error. Meaning you are not allowed to change the table if your change is effecting the view. If you need to change the table you need to drop the view first.

I like this approach while you getting an error before you change the table. I am pretty sure your users also do like this approach.