SQL SERVER – Modifying Table Used In SCHEMABINDING View

While I enjoy working on the Comprehensive Database Performance Health Check, I often love answering the question about blog posts as well. Just earlier I posted about What is SCHEMABINDING in SQL Server Views? and I received a very interesting question from the user. The question is about modifying the table used in the SCHEMABINDING View.

The question was it is very clear that we can’t drop the table used in the view but what about modifying the table which was used inside the SCHEMABINDING View. Well, the answer to this question is very simple. The columns which are used the views can’t be modified but the columns which are not used in the view can be easily modified. Let us understand the situation with the help of a quick demonstration.

First, let us create a table and view where we will use SCHEMABINDING.

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

Now we know that there are two columns in our table 1) ID and 2) Col1. Now let us try to modify each of them one at the time.

Modifying Column Used in SCHEMABINDING View

When we modify the column used in the view with the Schemabinding keyword, it will give us an error.

ALTER TABLE dbo.Table1
ALTER COLUMN ID BIGINT;

Msg 5074, Level 16, State 1, Line 11
The object ‘FirstView’ is dependent on column ‘ID’.
Msg 4922, Level 16, State 9, Line 11
ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column.

Modifying NOT Column Used in View

When we modify the column NOT used in the view with the Schemabinding keyword, it will NOT give us an error.

ALTER TABLE dbo.Table1
ALTER COLUMN Col1 BIGINT;

Commands completed successfully.

Summary

I have met lots of people who think that they can’t modify any part of the table which is used in the Schemabinding. That is actually not true. You can’t modify the columns which are used in the view but you can, of course, modify the columns which are not used in the view.

Did you know this one? Please leave a comment.

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