Cyber Monday Deal : Flat 30% OFF! + free self-paced courses  - SCHEDULE CALL

sddsfsf

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.

cta14 icon

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

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?

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

5 days 14 Dec 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

11 days 20 Dec 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

5 days 14 Dec 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

5 days 14 Dec 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

4 days 13 Dec 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

5 days 14 Dec 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

8 days 17 Dec 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

11 days 20 Dec 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

12 days 21 Dec 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

5 days 14 Dec 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

18 days 27 Dec 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

11 days 20 Dec 2024