Schema Binding: Views

Schema Binding is there to stop the accidentally removal of required columns from a table. If SCHEMABINDING is in the create statement of an object it will force SQL Engine to check any changes on the tables against the dependencies to make sure no Schema Bound objects are in conflict with the update. SQL Server does not check dependencies when altering or dropping tables.

Schema Binding can be used with:

• Functions
• Views

One of the benefits is the depended object can not be accidentally dropped. SQL Server will return an error as we will see in examples below. Schema Binding exists in SQL Server 2000, 2005, and 2008. In this post I will only address Schema Binding on Views.

Requirements to use Schema Binding for Views:

• Two-part name of the object (ex. dbo.Table1).
• Cannot select fields using “*”, must list fields being selected.
• Tables/Views in the Schema Binding cannot be dropped and alter statement affecting the tables and views which are dependencies for another object will fail.

There are no additional requirements added since the introduction of Schema Binding in SQL Server 2000. The demo below is done in SQL Server 2005.

We will first create a new table which will be used as a base for the views and function calls:

CREATE TABLE SBExample (SBCol1 int,
           SBCol2 int,
           SBCol3 int)
GO

We will first test out the two-part name and “*” (selecting all fields) conditions to see how SQL Server Engine reacts:

CREATE VIEW SBView WITH SCHEMABINDING
AS

SELECT *
FROM SBExample

GO

Executing the above query, SQL Server returns the following error message:

Msg 1054, Level 15, State 6, Procedure SBView, Line 4
Syntax ‘*’ is not allowed in schema-bound objects.

This requirement message exists to prevent people from binding all the columns in a table; when we create a view, we should be selecting only the required columns. So when you are using Schema Binding in Server Server, the SQL Engine expects a column list. You can still list all the columns if you choose to do so; but you have to cautious because this can run into performance issues. If you are selecting extra columns then needed then SQL Server will not be able to use the proper index (covering or including), thus possibly causing a full table scan to retrieve the information requested.

So let’s modify the code to following query and run again:

CREATE VIEW SBView WITH SCHEMABINDING
AS

SELECT SBCol1
FROM SBExample

GO

This time when we run it we get a different error message:

Msg 4512, Level 16, State 3, Procedure SBView, Line 4
Cannot schema bind view ‘SBView’ because name ‘SBExample’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

This is another restriction on the views; this forces you to bind the view to proper schema-table name combo. In general I have found it to be good practice when writing queries for development project to use at least two-part object name to eliminate confusion where the object resides. Therefore we can modify the code to following to fix this issue:

CREATE VIEW SBView WITH SCHEMABINDING
AS

SELECT SBCol1
FROM dbo.SBExample

GO

And this time our view is created successfully and we get the following message.

Command(s) completed successfully.

Now if we try to drop the table, SBExample, after binding one of the columns from to the view, SBView, lets see what happens.

DROP TABLE SBExample
GO

We get the following message:

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE ‘SBExample’ because it is being referenced by object ‘SBView’.

Notice this time SQL Server was forced to check the dependencies of the table before dropping it; since we were referencing a column from the table in the view, SBView, the SQL Server engine refused to let us drop the table. So let’s alter alter the view and re-create it without SCHEMABINDING.

ALTER VIEW SBView
AS

SELECT SBCol1
FROM dbo.SBExample

GO

Now let’s try to drop the table again:

DROP TABLE SBExample
GO

We get the following message this time:

Command(s) completed successfully.

What happened? We still have the view on top of the table? This time SQL Server didn’t complain when we tried to drop it because the SQL Engine wasn’t forced to check for dependencies. If we try to select from our view now, we should get an error message.

SELECT SBCol1
FROM SBView
GO

As expected we got the following error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dbo.SBExample’.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function ‘SBView’ because of binding errors.

As you can see this can cause issues, especially when you are creating views on third party applications or in applications in large organizations. When a third party vendor makes changes to their tables they have no way of knowing what dependencies exist outside their application. When they make their changes it can easily break the views that you might have created because the required columns or tables might have been dropped. If you have Schema Binding on your views; when implementing the update in the test environment you should be able to identify the issues and resolve them before going to production. In the bigger organization where you might be jumping from one project to another, you can’t be expected to know all the views and tables in the databases. Sometimes when altering the table you might drop something, which was required by another view. SQL Server does have a built-in manual dependencies checker, so anytime someone is dropping column/table it should be used to make sure it will not break any of the dependent objects.

Now let’s examine what happens to the table when we alter it while it is schema bound to a view. I re-created the original table with the original schema bound view. Now if I alter the table to add a new column, alter an existing column or drop a column; what do we expect to see? Does SQL Server engine allow us to execute it or does it error out?

ALTER TABLE SBExample
ADD SBCol4 int
GO

No Issues.
Now let’s try to alter few columns.

 ALTER TABLE SBExample
ALTER COLUMN SBCol4 bigint
GO

No Issues.

 ALTER TABLE SBExample
ALTER COLUMN SBCol1 varchar(1)
GO

So when we try to alter even just the type of the schema bound column we get an error:

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

So now let’s try to drop few columns.

ALTER TABLE SBExample
DROP COLUMN SBCol4
GO

No Issues.

ALTER TABLE SBExample
DROP COLUMN SBCol1
GO

If we try to drop the column for schema bound column we get the same error again:

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

So the lesson here is you can alter the table as much as you like as long as you don’t alter the columns that are being referenced. This is another reason why you should only reference the columns needed in the view so it does not stop other people from doing required changes to table. However as good as the schema binding option sounds; there is a draw back. You cannot drop or alter the column on any table that has object bound to it using schema binding. The objects will also need to be dropped before the table can be adjusted.

SQL Server 2005 and SQL Server 2008 books online state “SCHEMABINDING cannot be specified if the view contains alias data type columns”. I have tried everything I know to produce an error; as I far as I can tell this is an error in the books online. I posted on the Microsoft Community Newsgroup, Kalen Delaney (MVP) also thinks it is a mistake.

I then searched through Microsoft Connect to see if any Schema Binding issues were submitted relating to this. I could not find any so I submitted a bug report on Microsoft Connect. If anyone else can find another way to get this error let me know. I also went through System Error messages with no luck. I don’t think its a critical fix, but after playing with SQL Server 2000, 2005, and 2008 for while and not getting the expected results it was a bit frustrating; so I think it should be fixed in the books when time permits.

Microsoft Connect Bug Link: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383560

Nov. 24, 2008 – Update

Fixed a lot of my English (I hope) :). I would like to thank my friends for valuable feedback and corrections :-D.

Added examples for Alter Column. Thanks.