SQL Server View with Schema binding

Schema binding means binding the object with its schema. We know that a database object belongs to a schema. The default schema of the database object is dbo.

When a view is created it will have binding with the table which is used to create it. The table will belong to a schema. The table name must be written with its schema name else it will throw error.

When WITH SCHEMABINDING argument is passed during view creation then the table cannot be dropped unless its associated view is dropped. Note that dbo stands for schema of Employees table in the following example.

CREATE VIEW MyVwEmployees
with schemabinding
AS SELECT Id, first_name, last_name, salary
FROM dbo.Employees – dbo must be written
WHERE salary < 50000

Run the drop command to drop employees table.

drop table dbo.Employees

We find that it throws error message as given below.

Cannot DROP TABLE 'dbo.Employees' because it is being referenced by object 'MyVwEmployees'.