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 !!