SCHEMABINDING and Why It Can be Useful?

In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object. It also means that the underlying objects cannot be dropped. We can still modify those tables or views, as long as we don’t affect the definition of the schema-bound object (view or function).

If we reference a view or function in a schema-bound object (view or function), then the underlying view or function must also be schema-bound. And we cannot change the collation of a database if it contains schema-bound objects.

For schema binding, SQL Server requires a more specific and safer SELECT statement. There are a couple of restrictions, which we must remember:

1. We must use two-part names when referencing tables, views, or functions (SchemaName.ObjectName).

2. We cannot use SELECT *, so we must choose specific column names. Otherwise, we will get the following error:

Msg 1054, Level 15, State 6, Procedure vwName, Line x

Syntax ‘*’ is not allowed in schema-bound objects.

We cannot run sys.sp_refreshview on a schema-bound view (or sys.sp_refreshsqlmodule on a schema-bound function). We will get an unhelpful error, something like:

Could not find object ‘vwName’ or you do not have permission.

In case we work in a small team, and no one else has access to our database, then SCHEMABINDING is not necessary, because everyone knows what changes are being made and what effect they will have.

Otherwise, any change to the schema of an object can easily break the code of some view or function. If those changes are not properly controlled and tested, they might have a significant effect on business continuity.

One of the options to prevent such a disaster is to use WITH SCHEMABINDING. It’s not an ultimate weapon against unexpected changes, but it’s certainly a viable option, and we should be familiar with it.

The risk of adding WITH SCHEMABINDING to a view or function is that it might break deployment scripts.

Let's say that your team developed a new feature that includes dropping column X from table Y and removing the column from a view that references table Y. If the DROP COLUMN command comes before the ALTER VIEW command in the deployment script, and the view is schema-bound, then the DROP COLUMN command will fail, and so will the entire deployment.

The solution is to run the commands in the deployment script in the right order. But if that's not possible, then you should not use WITH SCHEMABINDING.

Another reason you might want to use WITH SCHEMABINDING is to allow SQL Server to inspect the code of the relevant view or function and determine some properties, such as whether the module is deterministic or not. When you create a view or function without SCHEMABINDING, then SQL Server doesn't bother to inspect the code, and it assumes the worst case, e.g. that the module is non-deterministic. Adding WITH SCHEMABINDING allows SQL Server to determine the actual properties of the module.

And why should you care? Because in some cases, SQL Server can apply optimizations to "better" views or functions (such as deterministic modules). And since it assumes the worst case if you don't add WITH SCHEMABINDING, then you might gain some performance improvement by just adding it.

In some cases, such as indexed views, SQL Server forces you to use WITH SCHEMABINDING.

We can check if an object is schema-bound using the system function OBJECTPROPERTY (object_id , 'is_schema_bound'). Additionally, we can retrieve all the schema-bound objects in a database by using sys.sql_modules:

SELECT
	*
FROM
	sys.sql_modules
WHERE
	is_schema_bound <> 0;

Thanks for reading!