What Does “Schema-Bound” Mean in SQL Server?

In SQL Server, “schema-bound” refers to a specific feature that ensures a SQL object, such as a view or function, is tightly linked to the schema of the underlying tables or other database objects it references. When a view or function is schema-bound, the underlying tables or objects cannot be altered in a way that would break the view or function’s dependencies.

How It Works

When you create a view or function with the SCHEMABINDING option, SQL Server locks down the structure of the underlying objects (like tables) used in that view or function. For example, if you attempt to drop or modify a column from a table that a schema-bound view references, SQL Server will prevent this operation until the schema-bound dependency is removed.

This ensures that the structure (schema) of the underlying objects remains consistent, protecting dependent objects from structural changes.

A schema-bound dependency also occurs when a table references another object through a CHECK or DEFAULT constraint or in the definition of a computed column (Microsoft).

Example of a Schema-Bound View

Let’s say we have two tables: Products and Categories. We want to create a view that joins these tables to display information about products and their categories, and we want to ensure the underlying tables cannot be modified in a way that breaks this view.

In that case we could create a schema-bound view, like this:

CREATE VIEW dbo.vw_ProductInfo
WITH SCHEMABINDING
AS
SELECT 
    p.ProductID, 
    p.ProductName, 
    p.CategoryID, 
    c.CategoryName
FROM dbo.Products AS p
JOIN dbo.Categories AS c
    ON p.CategoryID = c.CategoryID;

The part that binds the view to the schema is the bit that goes WITH SCHEMABINDING. If we didn’t want the view to be schema-bound, then we would simply omit the WITH SCHEMABINDING part.

When we use the WITH SCHEMABINDING option, SQL Server ensures that we cannot alter or drop the columns ProductID, CategoryID, or CategoryName in the Products or Categories tables without first removing the schema binding from the view.

When we use SCHEMABINDING, the SELECT statement must include the two-part names (like dbo.Products) of tables, views, or user-defined functions that are referenced. Also, all referenced objects must be in the same database.

Attempting to Alter a Column

For example, if we try to modify the ProductName column in the Products table after creating the above schema-bound view, we get an error:

ALTER TABLE dbo.Products
ALTER COLUMN ProductName NVARCHAR(500);

Output:

Msg 5074, Level 16, State 1, Line 1
The object 'vw_ProductInfo' is dependent on column 'ProductName'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ProductName failed because one or more objects access this column.

We actually got two errors; both related to the schema-binding. We got these errors because the view is schema-bound, preventing changes to the structure of the underlying table.

How to Alter the Column

If we really must alter a column in the underlying table, then we have two options:

Alter the view so that its definition no longer specifies schema-binding. Then restore schema-binding once the base table has been changed. This may or may not require a change to the view definition, depending on what changes were done to the base table.
Drop the view and then re-create it to reflect the new table definition.

Option 1: Alter the View

Here’s what removing schema-binding and then restoring it after the change looks like:

--Remove schema-binding from the view
ALTER VIEW dbo.vw_ProductInfo
AS
SELECT 
    p.ProductID, 
    p.ProductName, 
    p.CategoryID, 
    c.CategoryName
FROM dbo.Products AS p
JOIN dbo.Categories AS c
    ON p.CategoryID = c.CategoryID;
GO

--Change the base table
ALTER TABLE dbo.Products
ALTER COLUMN ProductName NVARCHAR(500);
GO

--Restore schema-binding on the view
ALTER VIEW dbo.vw_ProductInfo
WITH SCHEMABINDING
AS
SELECT 
    p.ProductID, 
    p.ProductName, 
    p.CategoryID, 
    c.CategoryName
FROM dbo.Products AS p
JOIN dbo.Categories AS c
    ON p.CategoryID = c.CategoryID;
GO

Removing schema-binding was just a matter of altering the view to have exactly the same definition, but without the WITH SCHEMABINDING part.

In other words, all we did was remove WITH SCHEMABINDING, change the table, then add WITH SCHEMABINDING again.

In this case the view’s definition remained the same, but if we had done something like, add a column, then we may have needed to add that column to the view too.

Option 2: Drop the View (and recreate it)

With this option we simply drop the view, make the change to the base table, and then create the view again:

--Drop the view
DROP VIEW dbo.vw_ProductInfo;
GO

--Change the base table
ALTER TABLE dbo.Products
ALTER COLUMN ProductName NVARCHAR(200);
GO

--Restore schema-binding on the view
CREATE VIEW dbo.vw_ProductInfo
WITH SCHEMABINDING
AS
SELECT 
    p.ProductID, 
    p.ProductName, 
    p.CategoryID, 
    c.CategoryName
FROM dbo.Products AS p
JOIN dbo.Categories AS c
    ON p.CategoryID = c.CategoryID;
GO

Again, in this case the view’s definition remains the same, but we could easily have incorporated any other changes to its definition if required.

Attempting to Drop the Base Table

We can’t drop the base table of a schema-bound object unless we either drop the schema-bound object first, or remove schema-binding from it.

Here’s what happens when I try to drop the base table:

DROP TABLE dbo.Products;

Output:

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Products' because it is being referenced by object 'vw_ProductInfo'.

As mentioned, we would need to drop the schema-bound view or alter it so that it’s no longer schema-bound.

Why Schema-Binding is Useful

Here are some of the main benefits of using SCHEMABINDING:

Data Integrity: Schema-binding ensures that a view or function will always be in sync with the structure of the underlying tables, preventing schema modifications that might break them.
Performance Optimization: Some schema-bound objects may perform better. Here are two articles that demonstrate this on schema-bound UDFs:
- SCHEMABINDING Performance Considerations (davewentzel.com)
- Using schema binding to improve SQL Server UDF performance (mssqltips.com)
Strong Dependency Control: Developers can have tighter control over dependencies in the database. This ensures that other developers or database administrators cannot unintentionally break a critical view or function by changing the underlying table structure.
View Indexing: A common use case for schema-bound views is that you can create an indexed view (materialized view) in SQL Server only if the view is schema-bound. Indexed views can significantly improve performance by storing the results of a view physically in the database, which SQL Server can then use to answer queries much faster.

Example of Indexed View

--Create the schema-bound view
CREATE VIEW dbo.vw_SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    s.SaleID, 
    s.TotalAmount, 
    s.SaleDate
FROM dbo.Sales AS s;

--Create an index against the view
CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary_SaleID
ON dbo.vw_SalesSummary(SaleID);

In this example, the view dbo.vw_SalesSummary is schema-bound and can now have a unique clustered index, which turns it into an indexed view.

Limitations of Schema-Binding

As we’ve seen in this article, schema-bound objects do come with limitations. Here’s a summary of the main limitations mentioned:

You must fully qualify all object names (include the schema name).
You cannot use SELECT * in schema-bound views or functions. You need to explicitly list all columns.
If you need to alter the structure of the underlying tables, you will need to first drop the schema-bound object or remove the schema binding.
All referenced objects must be in the same database.

Summary

Schema-binding is feature that binds an object to the underlying object/s that it references. This can help prevent the base objects from being changed in a way that would break the schema-bound object. Schema-binding a view will enable us to create an index on that view. And schema-bound objects may even come with a performance boost.

It’s always a good idea to review our database and see if there are any opportunities for improvements. Introducing schema-binding could be one such improvement.