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.
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)