Views with Check Option, Check Encryption and Schema Binding in SQL Server

Back to: SQL Server Tutorial

Check Option, Check Encryption and Schema Binding Options in SQL Server Views

In this article, I am going to discuss how to use the Check Option, Check Encryption, and Schema Binding Options in SQL Server Views with examples. Please read our previous article before proceeding to this article where we discussed the Complex Views in SQL Server with examples.

Example to understand how to use the Check Option, Check Encryption, and Schema Binding Options in SQL Server Views with an example:

We are going to use the following Department and Employee table to understand how to use the Check Option, Check Encryption, and Schema Binding Options in SQL Server Views.

Please use the below SQL Script to create and populate the Department and Employee table with sample data.

-- Create Department Table
CREATE TABLE Department
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50)
)
GO

-- Populate the Department Table with test data
INSERT INTO Department VALUES(1, 'IT')
INSERT INTO Department VALUES(2, 'HR')
INSERT INTO Department VALUES(3, 'Sales')

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

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

With check option in SQL Server Views:

In SQL Server, if a view is created by using a where condition and later on if any modification is performed on that view against the where condition still those changes are accepted. Let us understand this with an example. First, create a View that will retrieve the IT department employees from the Employee table by executing the below SQL statement.

CREATE VIEW vwITDepartmentEmployees 
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM Employee 
WHERE DeptID = 1

In the above example, the view vwITDepartmentEmployees is created with a where condition i.e. DeptID = 1 but if we trying to insert a record that violates the where condition, still that operation gets performed. Let’s try to insert one record other than the DeptID 1. The following INSERT statement Inserts a record with Deptid 1.
INSERT INTO vwITDepartmentEmployees (ID, Name, Gender, DOB, Salary, DeptID) VALUES(7, ‘Rohit’, ‘Male’,’1994-07-24 10:53:27.060′, 45000, 2)
And when we execute the above update statement the data gets inserted into the Employee table without any error. To see the data make a Select query against the Employee table by executing the below statement.
SELECT ID, Name, Gender, DOB, Salary, DeptID FROM Employee
Once you execute the above statement, you will get the following output.

As you can see the new record is inserted into the Employee table. If you want to restrict any DML operations on the view against the where condition, then you need to add “WITH CHECK OPTION” either at the time of creating the view, or we can add that option by altering the view in SQL Server.

Adding WITH CHECK OPTION in SQL Server Views

Alter the view vwITDepartmentEmployees to add the WITH CHECK OPTION by executing the below script.

ALTER VIEW vwITDepartmentEmployees 
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM Employee 
WHERE DeptID = 1
WITH CHECK OPTION

Now try to insert a record that violates the where condition by executing the below and sees what happens
INSERT INTO vwITDepartmentEmployees (ID, Name, Gender, DOB, Salary, DeptID) VALUES(8, ‘Mahesh’, ‘Male’,’1994-07-24 10:53:27.060′, 55000, 2)
Now when we try to execute the above Insert statement, the execution fails as the where clause condition of the view is violating and it will give the following error
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

With Encryption in SQL Server Views:

After creating a view we can see the text of the view that we have written under the text column of the syscomments table. But for any security reason if we don’t want to disclose that information to anyone then you can hide it by using the “WITH ENCRYPTION” option either while creating the view or altering the view. You can see the text of your view under the text column of the syscomments table as shown below.

SELECT id, ctext, text FROM 
SYSCOMMENTS 
WHERE ID = OBJECT_ID('vwITDepartmentEmployees')

Once you use the “With Encryption” option then you cannot view the text of the view using the sp_helptext system stored procedure. If you 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’.

The sp_helptext system procedure actually retrieves the text from the syscomments table (text column of the syscomments table which actually stores the text of our view). Here you can see the text of the view vwITDepartmentEmployees using the sp_helptext system defined Stored Procedure as shown below.

Now let’s modify the view to use the WITH ENCRYPTION option as shown below.

ALTER VIEW vwITDepartmentEmployees 
WITH ENCRYPTION
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM Employee 
WHERE DeptID = 1
WITH CHECK OPTION

Now if you verify the text of our view under the text column of the syscomments table, it will show null as shown below.

SELECT id, ctext, text FROM 
SYSCOMMENTS 
WHERE ID = OBJECT_ID('vwITDepartmentEmployees')

And if you use the sp_helptext system stored procedure to view the text it will give you the following error message

With SCHEMABINDING Option in SQL Server Views:

The SCHEMABINDING option specifies that the View 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 View definition. The view definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

So in simple words, we can say that, if a view is created by using the attribute “SCHEMABINDING” then we cannot drop or alter the table columns on which the view is dependent.

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

Let’s modify the vwITDepartmentEmployees view to use the SCHEMABINDING option as shown below

ALTER VIEW vwITDepartmentEmployees 
WITH SCHEMABINDING
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM dbo.Employee 
WHERE DeptID = 1
WITH CHECK OPTION

Note: When you want to use the SCHEMABINDING option, then you must have to specify each and every column name in the select statement but cannot use “*”. Moreover while using the SCHEMABINDING option the table name must be prefixed with the owner name i.e. dbo which tells the current user is only the owner of the table. 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 error as Cannot DROP TABLE ‘Employee’ because it is being referenced by object ‘vwITDepartmentEmployees’.

If required we can use the “WITH ENCRYPTION” and “WITH SCHEMABINDING” options at the same time as shown below.

ALTER VIEW vwITDepartmentEmployees 
WITH ENCRYPTION, SCHEMABINDING
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM dbo.Employee 
WHERE DeptID = 1
WITH CHECK OPTION

In the next article, I will discuss Indexed Views in SQL Server with examples. Here, in this article, I try to explain how to use the Check Encryption and Schema Binding Options in SQL Server Views step by step with some examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.