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 longs 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.
Let’s create a schema-bound view:
CREATE VIEW dbo.vwName WITH SCHEMABINDING AS SELECT T1.ID, T1.FirstName, T1.LastName, T2.Address, T2.Phone FROM dbo.Table1 AS T1 INNER JOIN dbo.Table2 AS T2 ON T1.Id = T2.Id GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE VIEW
dbo.vwName
WITH
SCHEMABINDING
AS
SELECT
T1.ID,
T1.FirstName,
T1.LastName,
T2.Address,
T2.Phone
FROM
dbo.Table1 AS T1
INNER JOIN
dbo.Table2 AS T2
ON
T1.Id = T2.Id
GO
For schema binding, SQL server requires a more specific and safer SELECT statement. There are a couple of restrictions, which we must remember:
We must use two-part names, when referencing tables, views or functions (SchemaName.ObjectName).
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.
P.S.: 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 ;
1
2
3
4
5
6
SELECT
*
FROM
sys.sql_modules
WHERE
is_schema_bound <> 0 ;
🙂
#SCHEMABINDING #sqlserver #change #schema #function #Useful #DBA #view