All About The Check Option in SQL Server Views

Introduction

A view is a virtual table based on the result set of an SQL statement. Since views are virtual tables, you can perform all the operations usually possible with a table using a view. For some instances, if the underlying table's mandatory fields are included in the view, it can also be used for update, delete and insert operations.Imagine a situation where you have written a view showing all the sales details for an amount greater than Rs. 5000. You have included all the mandatory fields in the view, which is a simple view. It can be used to insert, update and delete operations. Now if somebody tries to delete a record that satisfies the condition, the next time the view is called, it will not show that record. If this process continues, there will be a time when there will be no related records to show.SQL Server Views has introduced check options in view to avoid such issues. Over the following few paragraphs, we will learn about SQL View with check options and its purpose, with examples. You can be an expert in this field with a Microsoft sql server certification and learn online sql from the comfort of your home. 

What is Check Option in SQL Server?

WITH CHECK OPTION is an optional clause on the CREATE VIEW statement. It specifies the level of checking when data is inserted or updated through a view.

Purpose of With Check Option

The WITH CHECK OPTION is a CREATE VIEW statement option. The WITH CHECK OPTION ensures that all UPDATE and INSERTs satisfy the condition(s) in the view definition. The UPDATE or INSERT returns an error if they do not satisfy the condition(s). The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts to rows for which the WHERE clause in the select_statement is false.

You can even become a sql professional if you are interested in this career. Skyrocket your career with decent job opportunities that will come your way.

Example of Check Option in SQL

Let us take the Adventureworks database and SalesOrderDetail table.

The query to view SalesOrderDetail looks like below

select * from [Sales].[SalesOrderDetail]

The output looks like below

Now let us create a view without with Check option

create view view_check
as
select * from [Sales].[SalesOrderDetail] where UnitPrice >500
/pre<>

The output of the view is as below:

This view shows all the records which have unit prices greater than 500.Since this view has all the fields included in it and it is simple, it can be used for insert, update and delete operations.Now let us update a record, for example, where SalesOrderDetailId is 70 with a unit price value less than the logical value ie 400.

update view_check set Unitprice=400 where SalesOrderDetailID=70

Since no checks are incorporated in the view, the system will allow us to update the record.

So when the next time we run the view, that record will not be shown in the dataset.

But the record is definitely there in the table.

So, if we continue making these changes, which is beyond the scope of the view, there will be nothing left to show at one point. That is something that cannot be allowed. To prevent that, we implement the with check option in the view.

Let us now create the same view with the With Check Option.

create view view_check
as
select * from [Sales].[SalesOrderDetail] where UnitPrice >500
with check option

Now let us try to repeat the same update process with SalesOrderid 72

update view_check set Unitprice=400 where SalesOrderDetailID=72

This is what the output looks like.

This means the system has prevented the update from happening. You can always incorporate the update directly from the table but not from the view.This is the utility of With Check Option in SQL Server View. This same check can be implemented for insert and delete operations as well.

SQL Testing Training

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now

Conclusion

Over the last few paragraphs, we have discussed With Check Option in SQL Server Views. We have understood how to implement them in a view, their usage, and examples. We hope this is a good learning experience for those who want different views. If you want to explore more in this area more and become an expert, we suggest you enroll for an online sql certification and pride yourself on a sql server certification. You can even land a lucrative job with a handsome salary in this sql career path. Exciting, isn't it?