SchemaBinding – What & Why


August 6, 2014 by Kenneth Fisher


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 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))
SELECT Id, Col1, Col2 FROM Bound.Table1
SELECT Table2.Id, Table2.Col1, Table2.Col2
FROM Bound.Table2
JOIN Bound.Table3
	ON Table2.Id = Table3.Id;

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.

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


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


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 ALTER COLUMN Col3 varchar(51);

And these don’t:

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

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



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.

28 thoughts on “SchemaBinding – What & Why

  1. […] 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 […]

  2. Diana says:

    Another situation in which the schemabinding is useful

  3. Diana says:

    And thank you, it’s a good read 🙂

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

  5. Huzaifa says:

    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.

    • 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 🙂

      • Huzaifa says:

        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.

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

  6. Marcelo Barbieri says:

    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.

  7. Kris Horrocks says:

    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…

  8. […] using SQL Data Warehoue and Parallel Data Warehouse. It’s just a stripped down version. No SCHEMABINDING, no ENCRYPTION […]

  9. Amsalu Tialye says:

    Thanks for your contribution.

  10. Absolute says:

    I couldn’t find any resources on SCHEMABINDING until I came across this! Thank you.

  11. Thank you for this article! Very useful.

  12. […] one has added indexed views, stored procs, or functions with schemabinding atop your […]

  13. […] where it doesn’t work. You can’t schemabind anything with them, you can’t use a synonym as the base for another synonym, you can’t […]

  14. emi says:

    Thank you so much. It makes a lot of sense

  15. Malcolm says:

    Thanks for your contribution.

  16. Raj says:

    Thank you for this document. It’s clear and to the point.

Leave a Reply to Kenneth Fisher Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: