SCHEMABINDING View: What You Can Change in the Table

A SCHEMABINDING view locks the columns it uses, and only those columns. The rest of the table stays open for changes.

What SCHEMABINDING Does

A view is a stored query. Without a binding, you can drop a column that the view uses. The view then breaks later, when someone runs it. WITH SCHEMABINDING moves that failure to the moment of the change. SQL Server refuses any change to the table that would hurt the view.

The binding has two requirements. The view must use two-part names, such as dbo.Customers, and it can’t use SELECT *. An indexed view must be schema bound, so every indexed view carries this lock.

A common question follows. The table can’t be dropped while the view exists, but can its other columns still change? The answer is yes for the columns that the view doesn’t use. The demo shows both sides. It creates a database named SchemaBindDemo, a customer table and a bound view that reads two of its four columns. A second view, CustomerNotes, reads the Notes column and has no binding, for comparison.

IF DB_ID(N'SchemaBindDemo') IS NULL CREATE DATABASE SchemaBindDemo;
GO
USE SchemaBindDemo;
GO
DROP VIEW IF EXISTS dbo.CustomerNames, dbo.CustomerNotes;
DROP TABLE IF EXISTS dbo.Customers;
CREATE TABLE dbo.Customers (
    CustomerID int          NOT NULL PRIMARY KEY,
    FullName   nvarchar(60) NOT NULL,
    City       nvarchar(40) NULL,
    Notes      varchar(100) NULL
);
INSERT INTO dbo.Customers (CustomerID, FullName, City, Notes)
VALUES (1, N'Maya Lopez', N'Austin', 'Regular'), (2, N'Leo Brennan', N'Denver', NULL);
GO
CREATE VIEW dbo.CustomerNames
WITH SCHEMABINDING
AS
SELECT CustomerID, FullName FROM dbo.Customers;
GO
CREATE VIEW dbo.CustomerNotes
AS
SELECT CustomerID, Notes FROM dbo.Customers;

Change a Column the View Uses

Widen FullName from 60 to 200 characters. The view reads that column, so SQL Server refuses.

ALTER TABLE dbo.Customers ALTER COLUMN FullName nvarchar(200) NOT NULL;

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

Message 5074 names the view that blocks the change. Message 4922 repeats the failed statement. Nothing in the table changed.

Change a Column the View Doesn’t Use

The other columns are free. The next statements widen City, add a new column and drop Notes. None of them touches a column that the SCHEMABINDING view reads, so all three succeed.

ALTER TABLE dbo.Customers ALTER COLUMN City nvarchar(100) NULL;
ALTER TABLE dbo.Customers ADD Region nvarchar(20) NULL;
ALTER TABLE dbo.Customers DROP COLUMN Notes;

SELECT c.name AS ColumnName, TYPE_NAME(c.user_type_id) AS DataType, c.max_length AS MaxLength
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.Customers')
ORDER BY c.column_id;

City now holds 100 characters, which is 200 bytes, and Region exists. Notes is gone. FullName is still 60 characters, or 120 bytes.

What Happens Without the Binding

The Notes column was read by the second view, which has no binding. SQL Server let the drop through, and the damage waits for the first reader. Query that view now.

SELECT CustomerID, Notes FROM dbo.CustomerNotes;

The query fails with message 207, Invalid column name ‘Notes’. Message 4413 follows, which says that the view couldn’t be used because of binding errors. This is the failure that a binding moves to the day of the change. Without it, the report breaks on the day somebody opens it.

What Else the Binding Blocks

Dropping the column fails with the same two messages. Dropping the table fails with a message of its own, and renaming the column fails too. The next script runs all three, and every one of them fails.

ALTER TABLE dbo.Customers DROP COLUMN FullName;
GO
DROP TABLE dbo.Customers;
GO
EXEC sys.sp_rename N'dbo.Customers.FullName', N'CustomerName', N'COLUMN';

Msg 5074, Level 16, State 1, Line 1
The object 'CustomerNames' is dependent on column 'FullName'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN FullName failed because one or more objects access this column.
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Customers' because it is being referenced by object 'CustomerNames'.

The rename fails with message 15336, which says that the object participates in enforced dependencies. Renaming a column that the view uses is blocked as well as dropping it.

See Which Columns Are Bound

You can ask the catalog before you change anything. The view sys.sql_expression_dependencies marks schema bound references. The query labels each column of the table as bound or free.

SELECT c.name AS ColumnName,
       CASE WHEN EXISTS (SELECT 1 FROM sys.sql_expression_dependencies AS d
                         WHERE d.referenced_id = c.object_id AND d.referenced_minor_id = c.column_id AND d.is_schema_bound_reference = 1)
            THEN N'Bound' ELSE N'Free' END AS BindingStatus
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.Customers')
ORDER BY c.column_id;

Find Every Schema Bound Object

Views aren’t the only objects that can be bound. User-defined functions can use WITH SCHEMABINDING too, and a persisted computed column that calls a function needs it. One catalog view lists them all. The flag is_schema_bound in sys.sql_modules is 1 for each bound view, function or procedure of the database.

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName, OBJECT_NAME(m.object_id) AS ObjectName, o.type_desc AS ObjectType
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.is_schema_bound = 1
ORDER BY SchemaName, ObjectName;

Run it at the start of every table change project. Each row is an object that can refuse your ALTER TABLE.

Change a Bound Column Safely

Sometimes a bound column must change. The route is to remove the binding, change the column and bind the view again. Do all three in one transaction, so no one sees an unbound view in between. If a step fails, run ROLLBACK TRANSACTION and start again, because the GO lines let the later batches run anyway. ALTER VIEW must be the only statement in its batch, which is why the script has GO lines.

BEGIN TRANSACTION;
GO
ALTER VIEW dbo.CustomerNames AS SELECT CustomerID, FullName FROM dbo.Customers;
GO
ALTER TABLE dbo.Customers ALTER COLUMN FullName nvarchar(200) NOT NULL;
GO
ALTER VIEW dbo.CustomerNames WITH SCHEMABINDING AS SELECT CustomerID, FullName FROM dbo.Customers;
GO
COMMIT TRANSACTION;

Afterward, FullName is 200 characters, which is 400 bytes, and the view is schema bound again. ALTER VIEW drops the indexes of a view, so an indexed view needs its index created again at the end. Run the whole script in a quiet period, because ALTER TABLE takes a schema lock.

Is the Lock Worth It?

You could argue that SCHEMABINDING is too strict, because it blocks routine changes. That’s fair. The same strictness stops a column change from breaking a report at run time. A SCHEMABINDING view turns a surprise at run time into an error on the day of the change. An indexed view can’t exist without it.

What to Remember

Plan table changes around the bound columns. Free columns change as usual. A bound column needs the three-step route in one transaction, and an indexed view needs its index again. Use the bound-or-free query before you write the change script.

When you finish, drop the demo database.

USE master;
GO
IF DB_ID(N'SchemaBindDemo') IS NOT NULL
BEGIN
    ALTER DATABASE SchemaBindDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SchemaBindDemo;
END;

A SCHEMABINDING view is not a restriction on the table, it is a promise about the columns it reads.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.