SchemaBinding In SQL Server

Generally we create User Defined Functions (UDF) to define custom functions which accepts parameters and always return a value. In the same way we also create Views which acts as virtual table; compiles at run time and provides result set. It can be applied in one or more tables. At the time of creating UDF or Views, SQL Server provides one feature SchemaBinding which helps to improve performance. Now you think that why I discussed about UDF and Views because SchemaBinding can be applied to UDF and Views only (we will discuss both in this article).

In this article we will discuss SchemaBinding and its advantages.

What is SchemaBinding

The SchemaBinding option indicates that your UDF or View will be strictly bound to database objects. Not only helps to bind database objects strictly but also improve performance of query execution plan. This option can be provided at the time of creating UDF/View.

Let’s see how it can be applied in UDF and View:

In Function

See the following query for creating a UDF with SchemaBinding option.

CREATE FUNCTION dbo.fnGetUserID (@name varchar(10))
RETURNS INT
WITH RETURNS NULL ON NULL INPUT,
SCHEMABINDING AS
BEGIN
DECLARE @tempID INT
SELECT @tempID = ID
FROM dbo.tblUser
WHERE FirstName = @name;
RETURN @tempID;
END;

Once query executed successfully, the function created and strictly bound to database objects. As it is strictly bound to objects any kind of operation like drop or modify table will throw an error. Let’s see!

In above example we have created the UDF which depends on tblUser table and now we will try to drop the table directly. See the following Figure1 it is throwing an error and saying that it can’t be dropped since it is referenced in fnGetUserID.

Figure 1: Error in Delete Table

Let’s rename the column of tblUser table. See the following Figure2, it is throwing error since it can’t be applied because function fnGetUserID is bound to this column.

Figure 2: Error when Rename a Column

Let's see how SchemaBinding can be implemented in Views.

In Views

Same way we can use with SchemaBinding option while creating a View. See the following query:

CREATE VIEW vw_Test WITH SCHEMABINDING AS
SELECT ID FROM dbo.tblUser;

Here we create view based on table tblUser. Now let’s drop the table tblUser and see what happens. Strange it is throwing an error. Ohh, our view is created with SchemaBinding option.

Figure 3: Error when delete a table

Note1: You can’t use “*” in views when you use SchemaBinding. See the following query, if you will execute, it will throw error.

CREATE VIEW vw_Test WITH SCHEMABINDING AS
SELECT * FROM dbo.tblUser;

Note 2: If you want to create index on view then your view must be Schema bound.

From above discussion we come to know that UDF/View is strictly bound with database objects when SchemaBinding option is turned on.

Secondly, SchemaBinding helps to improve performance of UDFs and Views. When a object is SchemaBound, query optimizer doesn’t generate unnecessary spool operators for Query execution plan.

Spool operators helps query optimizer to avoid logical problems and perform queries better. Spool reads data and save it in out pre-defined TempDB database. This process is useful when a column volume is high or perform any complex calculation. Spool helps to store the result and use it in future purpose to improve performance. For more on Spooling, please visit here.

Take a look at the following example to create a simple UDF:

CREATE FUNCTION dbo.ComputeNum(@i int)
RETURNS int
BEGIN
RETURN @i * 2 + 50
END

In above UDF we didn’t provide SchemaBinding option. In that function we are not accessing any database objects (tables). So do we really need to add SchemaBinding option in this scenario? Yes, we need to add SchemaBinding option because when a function is not SchemaBinding there is no way to ensure that the underlying schema (including the schema of any underlying UDFs or Views that this UDF may call) did not change since its creation. This means SQL Engine has to derive these properties at runtime during every execution of the UDF. To avoid this performance penalty, we mark the UDF as SchemaBinding for safe side data access and do not attempt to derive these properties at runtime which leads to improve performance.

Advantages

It helps to improve query execution plan better.
It checks dependency objects before drop a table/view in database. Suppose a function is SchemaBinding to table. And you are trying to delete the table now; it will throw you an error because of schema binding.

Conclusion

This article described what is SchemaBinding and it can be applied to UDF and Views. And also we discussed it helps to improve performance. So use it when you are creating any UDF or View in your database.

Hope it helps you to understand an overview of SchemaBinding in SQL Server.

Happy Coding !!

5 Comments

Join the conversation! Your thoughts help the community grow.

Debendra Dash11y ago
nice one sir..
Santhakumar Munuswamy11y ago
Good one. Thanks for sharing
Karthikeyan K11y ago
Good one sir...Thanks for sharing
Rajeesh Menoth11y ago
Nice Share Manas Mohapatra
Pankaj Kumar Choudhary11y ago
Nice Explain Manas..........