SCHEMABINDING is an option that can be applied to Views or
Functions. When used, it forces SQL
Server to create dependencies between any objects referenced within the View or
Function, to the View or Function itself.
What this means is that the referenced objects cannot have their
definitions altered whilst the schema-bound View or Function exists, which
ensures the integrity of the View or Function.
Ok, so it means you have to alter the View or Function to
remove the schema binding if you want to change one of the referenced
objects. But is this really such a big
overhead? I see this as a good thing, as
it forces you to think about the impacts of the changes you are making. You are also forced to qualify all of your
tables in a View, and all objects in a Function (i.e. schema.object). Though I'm
sure everyone already does this as good practice, right?
Onto the benefits, and why we should go through the pains of
dealing with schema-bound objects....
Integrity - as
mentioned above, if an object is schema-bound then the objects it references
cannot have their definitions changed whilst the schema binding exists. This ensures the integrity of the View or
Function stopping poorly impacted changes being made. Note that SQL Server is intelligent enough to
recognise certain changes that do not affect the schema-bound View or Function,
e.g. if a number of columns from a table are referenced within a schema-bound
Function, then dropping a column not referenced in the Function would
still be allowed.
Performance - UDFs
can often be the cause of poorly performing queries, due to the query
optimizer's inability to look into the function to determine the best
plan. If the function is a simple scalar function
and does not reference any tables, without schema binding the optimizer must
assume data is being accessed and will add a spool operator to protect itself
from underlying data changes. Adding the
SCHEMABINDING option stops this occurring as the optimizer knows there is no
data access and will avoid the use of unnecessary spools. An article here
goes into more detail on the subject.
Unfortunately the performance improvements do not extend beyond
the example above, however the integrity benefits should be enough to warrant
the use of the option. Microsoft clearly
agree with this statement as they force the use of SCHEMABINDING for any Views
on which an index is to be created. I'm
a little baffled as to why the creation of an indexed view does not just create
the schema binding automatically, and I'd love someone to post a comment with
the answer to that question!
So there you have it; a simple way of improving scalar UDF
performance, and also ensuring a greater degree of database integrity.