(SEO is going to have a field day with this title.)

Anyways, in my holiday SQL documentation reading I came across an argument you can add when creating a view in Azure SQL Database, SCHEMABINDING. Adding this when creating a view prevents modifications to the base table or tables the view is using that would effect the columns being used by the view. It also prevents dropping the tables.

Let’s take a look at this with some examples. I’m going to be using an Azure SQL Database, hyperscale service tier for the following SQL code. Don’t know what a hyperscale SQL Database is? You can read more here, but to sum it up, its a database that will scale to your needs in the areas of compute, storage, and replicas. Up or down. And don’t let the name fool you, they are not expensive or only reserved for the most demanding workloads.

with SCHEMABINDING

I’ve created this database with the sample schema so we have some tables and data to start with.

Connect via Azure Data Studio and I am ready to go!

Next, let’s create the view we are going to use. We can join the Customer, Address and CustomerAddress tables:

create view [SalesLT].[vFullCustomerView]
with
    SCHEMABINDING
as
    select c.[CustomerID],
        c.[FirstName],
        c.[MiddleName],
        c.[LastName],
        a.[AddressID],
        a.[AddressLine1],
        a.[AddressLine2],
        a.[City],
        a.[StateProvince],
        a.[CountryRegion],
        a.[PostalCode]
    from [SalesLT].[Customer] c
        inner join [SalesLT].[CustomerAddress] ca
        on c.CustomerID = ca.CustomerID
        inner join [SalesLT].[Address] a
        on a.AddressID = ca.AddressID;
GO

And make sure the view works:

select * from [SalesLT].[vFullCustomerView]

View created, next let’s try and mess with the back end tables. First, alter some of the base tables, or try to at least.

alter table SalesLT.Customer
drop column FirstName;

Maybe a rename will work?

EXEC sp_rename 'SalesLT.Customer.MiddleName', 'MName', 'COLUMN';

The nuclear option! Drop all the tables!

drop table SalesLT.CustomerAddress;

As you can see, the SCHEMABINDING argument is a very useful addition to any view.

Not Every Word is a Safe Word

Something to be aware of is that if a column is not part of a view with SCHEMABINDING, you can alter or drop it. For example:

alter table SalesLT.Customer
drop column Title;

This code will drop that column on the Customer table. Also, if you rename a column not part of a view with SCHEMABINDING:

EXEC sp_rename 'SalesLT.Customer.SalesPerson', 'SalesPerson1', 'COLUMN';

That column will be renamed.

So while SCHEMABINDING will help in a lot of scenarios, its not going to replace good ol’ roles and permissions (don’t just give alter table out to anyone!).