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.

What is SCHEMABINDING in SQL Server Views? - Interview Question of the Week #245 schemabinding-800x226

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:

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

, , , ,
Previous Post
How to Download the Latest SQL Server Docker Image? – Interview Question of the Week #244
Next Post
How to Know If Index is Forced on Query? – Interview Question of the Week #246

Related Posts

Leave a Reply

Menu