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.

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

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, index, language sql, microsoft sql server, T-SQL