SchemaBinding – What & Why

August 6, 2014 by Kenneth Fisher

What

When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. So what does that mean? It means that as long as that schemabound object exists as a schemabound object (ie you don’t remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to.

That still sounds a bit confusing. This may be easier with an example (I like examples).

CREATE SCHEMA Bound 
CREATE TABLE Table1 (Id Int, Col1 varchar(50), Col2 varchar(50))
CREATE TABLE Table2 (Id Int, Col1 varchar(50), Col2 varchar(50))
CREATE TABLE Table3 (Id Int, Col1 varchar(50), Col2 varchar(50))
CREATE VIEW UnBoundView AS
SELECT Id, Col1, Col2 FROM Bound.Table1
CREATE VIEW BoundView WITH SCHEMABINDING AS
SELECT Table2.Id, Table2.Col1, Table2.Col2
FROM Bound.Table2
JOIN Bound.Table3
	ON Table2.Id = Table3.Id;
GO

So I’ve created three tables and two views under the schema Bound. The first view is unbound and references Table1 and the second view references tables Table2 and Table3 and is schemabound. I do want to point out a couple of things.

Because I created all the objects under the CREATE statement for the schema they are all contained within that schema.
When creating a schemabound view or function you have to use the two part name (include the schema name) for any tables or views you reference within it.

Next I’m going to try to drop a column referenced by each of the views.

ALTER TABLE Bound.Table1 DROP COLUMN Col2;
GO

This one works fine. Bound.Table1.Col2 is not referenced by any schemabound objects.

ALTER TABLE Bound.Table2 DROP COLUMN Col2;
GO

This one get’s an error.

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

One of my favorite parts of writing a blog is when I learn something new. In this case I had made the incorrect assumption that if a table was referenced by a schemabound function or view that you could not make any changes to it’s structure. Turns out that only the columns referenced by the function or view are bound.

So for example these work:

ALTER TABLE Bound.Table3 ALTER COLUMN Col1 varchar(51);
ALTER TABLE Bound.Table2 ADD Col3 varchar(50);
ALTER TABLE Bound.Table2 DROP COLUMN Col3;
ALTER TABLE Bound.Table2 ALTER COLUMN Col3 varchar(51);
ALTER TABLE Bound.Table2 ADD CONSTRAINT df_Tb2_Col1 DEFAULT 'A' FOR Col1

And these don’t:

ALTER TABLE Bound.Table2 ALTER COLUMN Col1 varchar(51);
ALTER TABLE Bound.Table2 DROP COLUMN Col2;

And here are a couple of other restrictions & factoids.

You cannot change the collation of a database with schemabound objects.
You cannot use SELECT * in a schemabound view.
You cannot run sp_refreshview on a schemabound view. You do get a rather unhelpful error though.
You can make any change to the table that do not affect the structure of the bound columns.
You can find out if an object is schemabound by looking at the column is_schema_bound in sys.sql_modules or the system function OBJECTPROPERTY(object_id, ‘is_schema_bound’).
If you reference a view or function in a schemabound view or function then that view or function must also be schemabound.
Objects that are bound (tables/views) cannot be dropped while a schemabound object references them

Why

Schemabinding isn’t a commonly used tool unless you are setting up an indexed view and then it can get lost in the crowd of other required restrictions. It does have uses outside of indexed views however. I could see using it if there is a mission critical view/function that just CAN’T break. By including the SCHEMABINDING clause you protect the view/function from unexpected changes to the tables underneath them. In fact if all of the data access in an application is through views and TVFs I might consider schemabinding all of them. It might be less important in a small shop with only a couple of developers and/or DBAs where everyone knows what changes are being made and what effect they will have. However if you are in a big shop with dozens of applications, may of which use the same databases, you can easily make a change to a table that breaks code in another application that you were completely unaware of.

So in the end SCHEMABINDING isn’t a world changing clause but still one that you should be aware of.

Edit: If you are interested Bert Wagner(t/b) has done a great video blog on this same subject.

Category: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, index, language sql, Microsoft SQL Server, T-SQL

29 thoughts on “SchemaBinding – What & Why”

BPOTW 2014-08-11 | SQL Notes From The Underground says:

August 11, 2014 at 9:42 AM

[…] considerations in SQL Server 2012 You aren’t using Resource Monitor enough by Scott Hanselman SchemaBinding – What & Why by Kenneth Fisher Deny vs Revoke by Kenneth Fisher SQL Server Database Projects and Team Foundation Build by Steven […]

Diana says:

August 11, 2014 at 10:07 AM

Another situation in which the schemabinding is useful
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx

Kenneth Fisher says:

August 11, 2014 at 10:14 AM

Great link! I wasn’t aware of that. I’ll have to give it a try myself.

Reply

Diana says:

August 11, 2014 at 10:08 AM

And thank you, it’s a good read 🙂

Kenneth Fisher says:

August 11, 2014 at 10:14 AM

Thanks!

Reply

I SCHEMABOUND my scalar UDF and you won’t believe what happened next. | SQL Studies says:

September 15, 2014 at 6:00 AM

[…] other week I did a post on SCHEMABINDING and Diana sent me this great link on SCHEMABINDING UDFs. In it TomErv discribes SCHEMABINDING […]

Huzaifa says:

September 11, 2015 at 5:40 AM

Great article.Learnt a lot.I have a qs.
How to modify data in a schema binding view using trigger?

Details:-I have a view which is created using schemabinding. On which we cannot perform DML commands .I needed to use trigger for this purpose.I am using sql.

Kenneth Fisher says:

September 11, 2015 at 6:04 AM

SchemaBinding has nothing to do with DML commands only the structure itself. Now some views aren’t updateable at all. In fact most aren’t. Only the simplest views are going to be updateable. I’ll try to get a post written on it in the near future 🙂

Reply
- Huzaifa says:
  
  September 11, 2015 at 6:09 AM
  
  Thank you..But my question is
  Is there way we can use triggers on schema bound view to perform DML commands.
  I guess there is some how.
  
  Reply
  - Kenneth Fisher says:
    
    September 11, 2015 at 6:11 AM
    
    The schemabinding on the view shouldn’t have anything to do with any triggers you apply to it. If you are having a problem you might consider posting it to a forum where you can include code and more of a description.

With Schemabinding – Czego się dziś nauczyłem says:

February 8, 2016 at 5:51 AM

[…] https://sqlstudies.com/2014/08/06/schemabinding-what-why/ […]

Marcelo Barbieri says:

February 17, 2016 at 6:10 AM

When using tSQLt Unit Test Framework, you will come across issues when using FakeTable method, which won’t allow you to fake a table that is linked to a view with schemabinding. Workaround will be needed.

Kenneth Fisher says:

February 17, 2016 at 9:49 AM

Good to know! Thanks!

Reply

Kris Horrocks says:

March 31, 2016 at 12:42 PM

Another scenario for schemabinding is with the new row-level security feature which I believe requires it for the reason you specified. You certainly don’t want the schema of tables used in security policies changed willy nilly…

Kenneth Fisher says:

March 31, 2016 at 12:46 PM

I haven’t really looked at the row level security yet. I’ll have to do that. Thanks for the tip 🙂

Reply