Avoid schema changes corrupting your viewsPosted: November 7, 2012
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.