Strangenut

What does schemabinding mean in SQL 2005 and 2008?

In order for us to be able to create an indexed view, we need to create that view with SCHEMABINDING specified. What exactly does SCHEMABINDING mean?  Here is the definition from MSDN:

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. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns.

What does this mean?

Well, to put it simply, once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view.  Examples of this would be removing columns or dropping tables that are specified in the view.

 


Posted Dec 02 2008, 10:06 AM by dacrowlah
Filed under: , ,

Comments

SQL Server Blog wrote Creating an Indexed View in SQL Server 2005 and 2008
on 12-02-2008 10:19 AM

As you probably are already aware, a view in SQL Server is generated off of a pre-defined select statement

Powered by Community Server (Non-Commercial Edition), by Telligent Systems