Avoid scema changes corrupting your views

7 min. læsetid
7. november 2012 Skrevet af: inspari Del med en ven     

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

<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">t2 </span><span style="color:gray;">(</span><span style="color:teal;">RowID </span><span style="color:blue;">INT IDENTITY</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span>1<span style="color:gray;">) </span><span style="color:blue;">PRIMARY KEY</span><span style="color:gray;">, </span><span style="color:teal;">Name </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">))
</span><span style="color:blue;">GO

CREATE VIEW </span><span style="color:teal;">v2 </span><span style="color:blue;">AS </span><span style="color:gray;">(</span><span style="color:blue;">SELECT </span><span style="color:teal;">RowId</span><span style="color:gray;">, </span><span style="color:teal;">Name </span><span style="color:blue;">FROM </span><span style="color:teal;">t2</span><span style="color:gray;">)
</span><span style="color:blue;">GO

SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">v2
</span><span style="color:blue;">GO

</span>

Then change the table, and select from the view.

<span style="color:blue;">ALTER TABLE </span><span style="color:teal;">t2 </span><span style="color:blue;">DROP COLUMN </span><span style="color:teal;">Name
</span><span style="color:blue;">GO

SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">v2
</span><span style="color:blue;">GO
</span>

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

image

Lets see what you should have done.

Lets start drop the view and table

<span style="color:blue;">DROP TABLE </span><span style="color:teal;">t2
</span><span style="color:blue;">GO

DROP VIEW </span><span style="color:teal;">v2
</span><span style="color:blue;">GO
</span>

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

<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">t2 </span><span style="color:gray;">(</span><span style="color:teal;">RowID </span><span style="color:blue;">INT IDENTITY</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span>1<span style="color:gray;">) </span><span style="color:blue;">PRIMARY KEY</span><span style="color:gray;">, </span><span style="color:teal;">Name </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">))
</span><span style="color:blue;">GO

CREATE VIEW </span><span style="color:teal;">v2 </span><span style="color:blue;">WITH SCHEMABINDING AS </span><span style="color:gray;">(</span><span style="color:blue;">SELECT </span><span style="color:teal;">RowId</span><span style="color:gray;">, </span><span style="color:teal;">Name </span><span style="color:blue;">FROM </span><span style="color:teal;">dbo</span><span style="color:gray;">.</span><span style="color:teal;">t2</span><span style="color:gray;">)
</span><span style="color:blue;">GO

ALTER TABLE </span><span style="color:teal;">t2 </span><span style="color:blue;">DROP COLUMN </span><span style="color:teal;">Name
</span><span style="color:blue;">GO
</span>

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.

image

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.