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.

Encryption:

When you create a user-defined function in SQL Server, you can choose to encrypt the function's definition. This means that the source code of the function will be stored in an encrypted format, making it more difficult for someone to view or modify the code. You can apply encryption to a function by specifying the "WITH ENCRYPTION" option when creating the function.

Syntax:

CREATE FUNCTION dbo.Function_NAME(
@Param1 DATATYPE, @Param2 DATATYPE)
RETURNS DATATYPE
WITH ENCRYPTION
AS
BEGIN
   -- Function body
END

Example:

Here we create the User-defined function GetNameByGender() which gives the Name of Employees by their Gender with an encryption option that encrypts the function definition.

CREATE FUNCTION dbo. 
GetNameByGender()(@Gender varchar(20))
RETURNS varchar(50)
WITH ENCRYPTION
AS
BEGIN
 RETURN (Select Name ,
 Gender from Employee
  where Gender=@Gender)
END

If we try to see the definition of the function using 'sp_helptext' it gives the message that 'The text for object 'GetNameByGender' is encrypted'.

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.