Contents
What does with Schemabinding do?
In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object.
What is Schemabinding a view in SQL?
SCHEMABINDING. Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
What is the meaning of schema binding?
Schema binding refers to the process of associating a database view to underlying tables in order to put indexes directly on the view. This may lead to great performance benefits when using the view; however, this tighter coupling is not without drawbacks.
What do you need to know about schemabinding in SQL?
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. This one works fine. Bound.Table1.Col2 is not referenced by any schemabound objects.
Can a schemabind view reference other schemabound views?
One downside is that if you schemabind a view, it can only reference other schemabound views. I know this because I tried to schemabind a view and was met with an error message telling me it could not be schemabound because one of the other views it references is not also schemabound.
What does it mean to have schemabound object in SQL?
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.
Why is schemabinding not a world changing clause?
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.