What is SCHEMABINDING in SQL Server Views? – Interview Question of the Week #245

Question: What does schema binding do for a view? It protects the view’s referenced objects from changes that would invalidate its definition. It doesn’t freeze all data or every possible change to a table.

The original demonstration is pleasantly direct. Create a table and a view that selects its ID column:

CREATE TABLE dbo.Table1 (ID int, Col1 varchar(100));
GO
CREATE VIEW dbo.FirstView WITH SCHEMABINDING
AS SELECT ID FROM dbo.Table1;
GO
DROP TABLE dbo.Table1;

The DROP fails with error 3729 because FirstView references the table through schema binding. A change that affects the referenced ID column is also restricted. Ordinary inserts, updates and deletes are still permitted; unrelated schema changes can be permitted too.

Now deliberately remove the binding:

ALTER VIEW dbo.FirstView AS SELECT ID FROM dbo.Table1;
GO
DROP TABLE dbo.Table1;
GO
SELECT * FROM dbo.FirstView;

The table can now be dropped. The final SELECT cannot resolve the view’s table, producing an invalid-object/binding error. Removing the protection did not repair the dependency.

Use two-part object names such as dbo.Table1 and explicitly named columns. The view’s referenced objects must be in the same database. Schema binding is a prerequisite for an indexed view, but adding this clause alone doesn’t materialize the view or create an index.

The snippets explain the sequence; they drop an object and are intended only for a disposable lab. The editable companion script adds a dedicated-database guard, refuses pre-existing names, catches the expected error and rolls back its demonstration objects. Run the sequence only in a disposable database; it intentionally attempts to drop a table.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.