Schema Binding
Schema binding is an option that you can use when creating a user-defined function to bind the function to the schema of the objects it references. This means that if you create a function that references tables or views in a specific schema, you can use the schema binding option to ensure that the schema of those objects cannot be changed. This can be useful in situations where you want to prevent accidental changes to the schema that could break the function. You can apply Schemabinding to a function by specifying the “WITH SCHEMABINDING” option when creating the function.
Syntax:
CREATE FUNCTION dbo.MyFunction (@Param1 INT, @Param2 VARCHAR(50)) RETURNS INT WITH SCHEMABINDING AS BEGIN -- Function body END
Example:
Let’s consider the above two tables Employee and EmployeeCITY, and create the function GetNameByCITY with a schema binding option :
CREATE FUNCTION [dbo]. GetNameByCITY(@CITY varchar(20)) RETURNS TABLE WITH SCHEMABINDING AS RETURN (Select E.Name,EC.CITY from [dbo].[Employee] E JOIN [dbo].EmployeeCITY EC ON E.ID=EC.E_ID where ec.CITY=@CITY)
Now if we try to change or update the records then it gives the error:
Note that once you apply schema binding to a function, you cannot modify the schema of the objects it references without first dropping the function.
Hence, Encryption and schema-binding are highly helpful from a security and integrity point of view and also improve the performance of the code.
Encryption and Schema Binding Option in User Defined Function
Encryption and schema binding are two separate options that can be applied to user-defined functions in SQL Server.
Contact Us