Back to: SQL Server Tutorial For Beginners and Professionals

Encryption and Schema Binding Option in SQL Server Functions

In this article, I will discuss the use of Encryption and Schema Binding Option in SQL Server Functions with some examples. Please read below articles before proceeding to this article.

Scalar Function in SQL Server

Inline Table Valued Function

Multi-Statement table Valued Function

WITH ENCRYPTION OPTION:

If you want to encrypt the text of a Function then you need to use the With Encryption Option which is provided by the SQL Server while creating the Function. Once we create the function using the “With Encryption” option then we cannot view the text of the function using the sp_helptext system stored procedure. If we try to view the text using the sp_helptext system stored procedure, then we will get a message stating ‘the text for the object is encrypted’.

Let understand the use of with encryption option with one example.

We are going to use the following Employee table

Please use below SQL Script to create and populate the Employee table

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO

Now let’s create an Inline Table Valued Function without using the With Encryption option

-- Function without with encryption option
CREATE FUNCTION fn_GetEmployeeDetailsById
(
  @ID INT
)
RETURNS TABLE
AS
RETURN (SELECT Name, Gender, CAST(DOB AS DATE) AS DOB 
    FROM Employee 
    WHERE ID = @ID)

Now let’s view the text using sp_helptext system stored procedure as shown below.

sp_helptext fn_GetEmployeeDetailsById

When we execute the above statement it will give us the below output

’Now let’s alter the above function to use the With Encryption option.

-- Function with encryption option
ALTER FUNCTION fn_GetEmployeeDetailsById
(
  @ID INT
)
RETURNS TABLE
WITH Encryption
AS
RETURN (SELECT Name, Gender, CAST(DOB AS DATE) AS DOB 
    FROM Employee 
    WHERE ID = @ID)

Now if we try to retrieve the text of the above function using the sp_helptex fn_GetEmployeeDetailsById.

You will get a message stating ‘The text for object ‘fn_GetEmployeeDetailsById’ is encrypted.‘ As shown in the below image.

With SCHEMABINDING Option:

The SCHEMABINDING option specifies that the function is bound to the database objects that it references. So, when the SCHEMABINDING option is specified the database object cannot be modified in any way that would affect the function definition. The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

Suppose we create a function that depends on the table Employee and after creating the function if we modify the table from the database while the function is referencing to that table. After modification of the table if we execute the function it will give an error message that is “Invalid Object Name Employee”

Let’s understand this with an example.

Let’s Create a Function without using the “With Schemabinding” option

-- Function without SCHEMABINDING option
CREATE FUNCTION fn_GetEmployeesByGender
(
  @Gender INT
)
RETURNS TABLE
AS
RETURN (SELECT Name, Gender, CAST(DOB AS DATE) AS DOB 
    FROM Employee 
    WHERE Gender = @Gender)

In the above example, the function fn_GetEmployeesByGender depends on the table Employee. Let’s delete the Employee table.

DROP TABLE Employee

So, here we are able to delete the table Employee, while the function “fn_GetEmployeesByGender” is still referencing it.

Once we delete the table, let’s try to execute the function as shown below.

SELECT * FROM dbo.fn_GetEmployeesByGender(‘Male’)

When we execute the above SQL Statement, it will give us the below error.

How to prevent this?

To prevent this, we need to use the SCHEMABINDING option while creating the function and the important thing to we need to remember is that we need to specify the two-part name of the table while we are using the SCHEMABINDING option.

Let’s create and populate the Employee table using the script provided at the beginning of this article. Once you create and populate the Employee table, let’s alter the Function to use the SCHEMABINDING as shown below.

-- Function with SCHEMABINDING option
ALTER FUNCTION fn_GetEmployeesByGender
(
  @Gender INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (SELECT Name, Gender, CAST(DOB AS DATE) AS DOB 
    FROM dbo.Employee 
    WHERE Gender = @Gender)

Now let’s try to delete the Employee table as shown below.

DROP TABLE Employee

When we execute the above statement, it will give us the below error.

If required, then we can use both the “WITH ENCRYPTION” and “WITH SCHEMABINDING” option at the same time as shown below.

CREATE FUNCTION fn_GetEmployeesByGender
(
  @Gender INT
)
RETURNS TABLE
WITH SCHEMABINDING, ENCRYPTION
AS
RETURN (SELECT Name, Gender, CAST(DOB AS DATE) AS DOB 
    FROM dbo.Employee 
    WHERE Gender = @Gender)

In the next article, I will discuss the Deterministic and Non-Deterministic Functions in SQL Server with some examples.

SUMMARY

In this article, I try to explain the Encryption and Schema Binding Option in SQL Server Functions step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.