SCHEMABINDING Views in SQL Server Example

By: Rajendra Gupta | Updated: 2023-03-30 | Comments (3) | Related: > Views

Problem

The tip, Views in SQL Server, explored the purpose of views, creating views examples, and benefits of views. A view is a virtual table that references the actual database tables stored in the database. What if someone changes the underlying table structure, such as renaming the column, adding a new column, or dropping the table? What is the impact of changing schema on views? How can we stop any schema changes if the view references the schema?

Solution

Schema-bound views are database objects in SQL Server that provide a layer of abstraction over tables or other views, allowing users to access data in a more structured and organized way. When a view is schema-bound, it is bound to the schema of the underlying table or view, which means that the schema cannot be modified without modifying the view.

In other words, schema-bound views are created with the WITH SCHEMABINDING option. This option binds the view to the schema of the underlying table or view, preventing any modifications to the schema that would invalidate the view. It ensures that the view always returns the same data and schema, which can improve query performance and prevent errors.

Schema-bound views also have other benefits, such as improved security and the creation of indexes on the view. However, they also have some limitations, such as restrictions on modifying the schema of the underlying tables and views and the inability to reference non-deterministic functions or views in the view definition.

Examples of using SCHEMABINDING for Views

Here are a few examples of creating schema-bound views in SQL Server.

Example 1: Create a Schema-bound View that Selects Columns from a Table

CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT Column1, Column2, Column3
FROM dbo.MyTable
WHERE Column1 > 0;

The view is bound to the schema of the MyTable table. It selects columns Column1, Column2, and Column3 from that table where Column1 is greater than 0.

Example 2: Create a Schema-bound View that Joins Two Tables

CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT T1.Column1, T2.Column2, T2.Column3
FROM dbo.Table1 AS T1
JOIN dbo.Table2 AS T2 ON T1.KeyColumn = T2.KeyColumn
WHERE T1.Column1 > 0;

This view is bound to the schema of Table1 and Table2 and joins the two tables on the KeyColumn column. It selects Column1 from Table1 and Column2 and Column3 from Table2 where Column1 is greater than 0.

Example 3: Create a Schema-bound View with Computed Columns

CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT Column1, Column2, Column3, Column1 * Column2 AS ComputedColumn
FROM dbo.MyTable
WHERE Column1 > 0;

The view is bound to the schema of MyTable and includes a computed column, ComputedColumn, which results from multiplying Column1 and Column2.

How SCHEMABINDING Helps Avoid Schema Changes

Let's create two views, one with and one without the SCHEMABINDING keyword.

View 1 -The first view, Vw_Employee, references table dbo.Employee.

Create view dbo.Vw_Employee
As
Select first_name, last_name from dbo.Employee
Go

View 2 -The second view, Vw_Employee _new, references table dbo.Employee_new with SCHEMABINDING option.

Create view dbo.Vw_Employee_new WITH SCHEMABINDING
As
Select first_name, last_name from dbo.Employee_New

Let's drop column last_name in table Employee.

Alter table Employee DROP COLUMN last_name

We were able to drop the column from the Employee table.

If we use the view now, you will get an error. The view cannot use the view vw_employee because the referencing column last_name, does not exist.

On the other hand, if we try to drop the column from the employee_new table, you get the error below. This is due to SCHEMABINDING we used while creating the view. It prevents you from dropping the column because vw_employee_new is dependent on column last_name.

Limitations and Rules of SQL Server Views with SCHEMABINDING

Schema-bound views in a database are views bound to the underlying schema of the tables they reference. These views have several rules and limitations to create and use them effectively. Here are some key rules and limitations of schema-bound views:

View definition cannot be altered: Once a schema-bound view has been created, its definition cannot be altered unless the view is first dropped and then recreated. Any changes to the view definition could potentially invalidate the binding to the underlying schema.
Tables cannot be dropped or altered: If a schema-bound view references a table, that table cannot be dropped or altered in any way that would affect the binding between the view and the table. It includes changes to column names, data types, or constraints.
Base tables only: A view with a SCHEMABINDING clause can only reference base tables. It cannot reference other views.
Two-part naming convention: The base tables must use the two-part naming convention. Example: dbo.employee
Must be specific: You must specify the column name in the SELECT statement of SCHEMABINDING views. Else, you get the error - “ Select * Syntax '*' is not allowed in schema-bound objects.”

It is essential to carefully consider these rules and limitations when creating schema-bound views to ensure proper functioning and avoid any unexpected behavior or errors.

Benefits of Using the SCHEMABINDING Views

Performance optimization: Schema binding can improve query and view performance by allowing the database engine to optimize query execution plans based on the knowledge that objects are bound together. It can result in faster query execution times and more efficient use of system resources.
Data integrity: Schema binding can help to ensure data integrity by preventing accidental changes to underlying objects that could result in data inconsistencies or errors. When an object is bound to a schema, the schema must be updated first before any changes can be made to the bound object.

Summary

There are some considerations to consider when using schema binding. For example, schema binding can limit the ability to change underlying objects and may not be suitable for all scenarios. It is essential to carefully evaluate the benefits and trade-offs of schema binding for each use case.

Next Steps

Explore views in SQL Server and be familiar with the syntax and usage.
Read the Microsoft documentation on views.
More articles about SQL Server Views

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-03-30

Benefits and Limitations of SCHEMABINDING Views in SQL Server