SchemaBinding – What & Why

23

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.

 
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 can not change the collation of a database with schemabound objects.
  • You can not use SELECT * in a schemabound view.
  • You can not 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) can not 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.

23 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:

    And thank you, it’s a good read 🙂

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

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

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

  6. 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…

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

  8. Amsalu Tialye says:

    Thanks for your contribution.

  9. Absolute says:

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

  10. Thank you for this article! Very useful.

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

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 1,664 other followers

Follow me on Twitter

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