Schema-Bound View in SQL server

A schema-bound view is a view created using the WITH SCHEMABINDING option that binds the view definition to the schema of the underlying objects (tables, other views).

Once schema-bound:

Ø  Underlying tables cannot be altered or dropped in ways that would break the view

Ø  Column metadata becomes fixed and deterministic

Ø  The view becomes eligible for indexed views

Ø  SQL Server treats the view as a contract, not just a query shortcut

SQL Server guarantees:

Ø  Underlying table schema cannot change

Ø  Columns cannot be dropped or altered

Ø  Metadata dependencies are frozen

Ø  View definition is deterministic

Ø  Indexed views become possible

To enforce this, SQL Server must know exactly which columns the view depends on.

Mandatory rules to creating schema bounded view

Ø  Two-part naming is mandatory

Ø  Select * is not allowed

Ø  All referenced objects must be schema- bounded too

Ø  No non-deterministic function

Ø  No temporary object

If any one rule is violated, creation fails.

Let’s see the below

Two-part naming is mandatory

Creating a view in default schema but not using schema (dbo) in the tables.

CREATE VIEW vEmployees_SchemaBound WITH SCHEMABINDING AS SELECT     EmployeeID,     Name,     DepartmentID,     Salary FROM Employees;

It will throw an error.

Now creating with schema

View created successfully.

Select * is not allowed

Select * is not allowed in schema bounded view

Let’s see the example

CREATE VIEW vEmployees_SchemaBound_selectAll WITH SCHEMABINDING AS SELECT     * FROM dbo.Employees;

We can’t create view

All Referenced Objects Must Be Schema-Bound Too

If a schema-bound view references another view. That view must also be schema-bound.  This creates a dependency chain.

Let’s see.

We have a normal view and want to use this view to create a schema bounded view.

CREATE VIEW vEmployees_normal AS SELECT     EmployeeID,     Name,     DepartmentID,     Salary FROM dbo.Employees;

Now creating a schema bounded view using this view.

CREATE VIEW dbo.vEmployees_SchemaBound_Using_View WITH SCHEMABINDING AS SELECT     EmployeeID,     Name,     DepartmentID,     Salary FROM dbo.vEmployees_normal;

It will throw an error

First, we need to create schema bounded view then we will use it in the another view.

CREATE VIEW dbo.vEmployees_SchemaBound_Using_View WITH SCHEMABINDING AS SELECT     EmployeeID,     Name,     DepartmentID,     Salary FROM dbo.vEmployees_SchemaBound;

Now view is created successfully.