SchemaBinding – What & Why28
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 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
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
28 thoughts on “SchemaBinding – What & Why”
Leave a Reply to Diana Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] 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 […]
Another situation in which the schemabinding is useful
Great link! I wasn’t aware of that. I’ll have to give it a try myself.
And thank you, it’s a good read 🙂
[…] other week I did a post on SCHEMABINDING and Diana sent me this great link on SCHEMABINDING UDFs. In it TomErv discribes SCHEMABINDING […]
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 🙂
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.
[…] https://sqlstudies.com/2014/08/06/schemabinding-what-why/ […]
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.
Good to know! Thanks!
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…
I haven’t really looked at the row level security yet. I’ll have to do that. Thanks for the tip 🙂
[…] using SQL Data Warehoue and Parallel Data Warehouse. It’s just a stripped down version. No SCHEMABINDING, no ENCRYPTION […]
Thanks for your contribution.
Any time 🙂
I couldn’t find any resources on SCHEMABINDING until I came across this! Thank you.
No problem. It’s a somewhat under rated setting in my opinion.
Thank you for this article! Very useful.
Thanks 🙂 Glad you liked it.
[…] one has added indexed views, stored procs, or functions with schemabinding atop your […]
[…] 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 […]
Thank you so much. It makes a lot of sense
Thanks for your contribution.
Thank you for this document. It’s clear and to the point.
Thanks 🙂 I appreciate it.