Use SCHEMABINDING In Views To Protect Against Alteration Or Dropping Of Tables

My post from last night triggered something in me to write a little bit about SCHEMABINDING.
If you create a view and someone drops one of the tables that the view is dependent on then the view will fail. How can you protect yourself against that? You can use WITH SCHEMABINDING. Here is how you use it:

Let's first create some tables and the view

CREATE TABLE dbo.Test1 (ID INT, SomeName VARCHAR(49))
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))

INSERT dbo.Test1 VALUES 1,'Name1')
INSERT dbo.Test1 VALUES( 2,'Name2')
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')

CREATE VIEW dbo.TestTables
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2

--SELECT from the view and everything is fine
SELECT *
FROM dbo.TestTables


-- Drop table test2
DROP TABLE dbo.Test2

--Let's do the select again
SELECT * FROM dbo.TestTables

--And this is the error message that we get back
Server: Msg 208, Level 16, State 1, Procedure TestTables, Line 3
Invalid object name 'Test2'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'TestTables' because of binding errors.

--Let's create the Test2 table again
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))

INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')

--Now let's alter the view and we will use SCHEMABINDING
ALTER VIEW dbo.TestTables WITH SCHEMABINDING
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2


--Try dropping the Test2 table again
DROP TABLE dbo.Test2

--And here is the message
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Test2' because it is being referenced by object 'TestTables'.

--Let's try altering the table by adding a column
ALTER TABLE dbo.Test2 ADD Col3 INT

-- That seems to work
SELECT * FROM dbo.Test2
SELECT * FROM dbo.TestTables


--Let's try altering the table by dropping a column
ALTER TABLE dbo.Test2 DROP COLUMN SomeOtherName

--Can't do that
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN SomeOtherName failed because one or more objects access this column.

--Let's try altering the table by changing a column size
ALTER TABLE dbo.Test2 ALTER COLUMN SomeOtherName VARCHAR(48)

--Same here; this is not allowed
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN SomeOtherName failed because one or more objects access this column.

--Let's drop the column that is not used by the view
ALTER TABLE dbo.Test2 DROP COLUMN Col3

--No problem here either, if a column is not used by the view it can be dropped


--Clean up the mess
DROP VIEW dbo.TestTables
DROP TABLE dbo.Test2, dbo.Test1

So there you have it; SCHEMABINDING is a nice way to protect your views from dropped tables, modified columns or dropped columns on tables that are used in views.

--You can add columns without a problem to the table used by a view.
--You can modify a column that is not used in a view.
--You can also drop a column that is not used in the view