WITH SCHEMABINDING Clause in SQL Server

In My Previous post (View in SQL Server), I’ve mentioned that a table can be deleted even though it is associated with normal view. Also we can modify that table design or drop that table without any notification or warning message.

Have a look at this simple scenario,

Here I’ve a table, Named as Employee with 3 columns Empno, Empname, Test.

I created a view with following definition,

CREATE VIEW ViewTest
AS
SELECT Empno,EmpName FROM dbo.Employee
GO
 

After created the view "ViewTest", I renaming the column EmpName to Empname2

Now If we access the views, after modified the schema of the base table, it will throw the error like

Msg 207, Level 16, State 1, Procedure ViewTest, Line 3
Invalid column name 'EmpName'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'ViewTest' because of binding errors.
 

Also it allows me the drop the base table.

DROP TABLE Employee
GO
 

If we access the views, after drop the base table, it will throw the error like
Msg 208, Level 16, State 1, Procedure ViewTest, Line 3
Invalid object name 'dbo.Employee'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'ViewTest' because of binding errors.

To avoid this sort of issues, SQL Server provided a option called SCHEMABINDING. This option will bind the table and schema together, also it is restricting the schema changes against on the base table. So we can avoid the error.

The following snippet helps, how to make a view with SCHEMABINDING

CREATE VIEW ViewTest WITH SCHEMABINDING
AS
SELECT Empno,EmpName FROM dbo.Employee
GO

Now if you try to change the column name of base table, then it will throw the following error

'Employee' table
- Warning: The following schema-bound objects will be modified:
  - View 'dbo.ViewTest': schema binding will be removed.

If you want to enforce the SCHEMABINDING  to existing view, you can follow the snippet given below

ALTER VIEW ViewTest WITH SCHEMABINDING
AS
SELECT Empno,EmpName FROM dbo.Employee
GO

Hope it helps you!