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

Question: What is SCHEMABINDING in SQL Server Views?

Answer: SCHEMABINDING keywords prevent tables used in the views to make any such modifications that can affect the view’s definition.

When this keyword is used in the view it binds the view to the schema of the underlying tables. If you want to modify the table definition which may affect the view, you may have to drop the view first and then change the table definition.

Let us see a simple example of the same behavior to understand it in detail.

CREATE TABLE dbo.Table1 (ID INT, Col1 VARCHAR(100))
GO
CREATE VIEW dbo.FirstView
WITH SCHEMABINDING
AS
SELECT ID
FROM dbo.Table1
GO

Now attempt to drop the table using the following syntax and it will give you an error.

DROP TABLE dbo.Table1
GO

Here is the error:

Msg 3729, Level 16, State 1, Line 10
Cannot DROP TABLE ‘dbo.Table1’ because it is being referenced by object ‘FirstView’.

If you want to drop the table, you will have to first either modify the view and remove the schema binding or just drop the view. Here is the script where the view is created without schema binding.

CREATE OR ALTER VIEW dbo.FirstView
AS
SELECT ID
FROM dbo.Table1
GO

Now when you attempt to drop the table it will work fine.

DROP TABLE dbo.Table1
GO

Commands completed successfully.

Now you can attempt to run the view and it will give you the following error:

SELECT *
FROM dbo.FirstView
GO

Msg 208, Level 16, State 1, Procedure FirstView, Line 4 [Batch Start Line 21]
Invalid object name ‘dbo.Table1’.
Msg 4413, Level 16, State 1, Line 24
Could not use view or function ‘dbo.FirstView’ because of binding errors.

I hope it is very clear from this example, how SCHEMABINDING works for the view and its impact on the objects which it is created with.

Here are a few additional blog posts related to Views which I had written earlier:

• SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
• SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
• SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
• SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
• SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
• SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
• SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
• SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
• SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
• SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
• SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11

Reference: Pinal Dave (https://blog.sqlauthority.com)