It’s usually a good idea to schema bind your views in SQL Server.

Schema binding your view will ensure that the underlying tables can’t be changed in a way that would affect the view. Without schema binding, the underlying tables or other objects could be modified or even deleted. If that happens, the view will no longer work as expected.

To create a schema bound view, use the WITH SCHEMABINDING in your T-SQL code for creating the view.

Example

Here’s an example of creating a schema bound view:

CREATE VIEW dbo.v_Cats
WITH SCHEMABINDING
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO

Notice I used the two-part name when referencing the table in my query – I used dbo.Cats instead of just Cats. Doing this is a requirement for schema binding an object. If you try to schema bind an object without using two-part names you’ll get an error.

Now that I’ve schema bound my view, if I try to drop the table referenced in its definition, I get an error:

DROP TABLE Cats;

Result:

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

Here’s what happens if I try to schema bind the view without using two-part naming:

CREATE VIEW dbo.v_Cats
WITH SCHEMABINDING
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM Cats;
GO

Result:

Msg 4512, Level 16, State 3, Procedure v_Cats, Line 5
Cannot schema bind view 'dbo.v_Cats' because name 'Cats' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Other View Attributes

Multiple attributes can be separated by a comma. You don’t need to specify WITH for each attribute.

For example, if you want the view to be schema bound and encrypted, you can do this:

CREATE VIEW dbo.v_Cats
WITH SCHEMABINDING, ENCRYPTION
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO