Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
Using Check Option
CHECK OPTION is a very handy tool we can use with our views. If I give you the definition right away and you don’t already know what it does then is just confusing. However the examples make perfect sense. So let’s save the definition for the end of this post. First let’s look at the creation of the vHighValueGrants view.
CREATE VIEW vHighValueGrants
AS
SELECT GrantName, EmpID, Amount
FROM [Grant]
WHERE Amount > 20000
JProCo already offered to do a $1,000 match for each grant larger than $20,000. The $1,000 match is currently included in our vHighValueGrants amounts as seen in the figure below.
We have just been informed that the campaign didn’t receive the necessary approval from the stakeholders. Therefore, we must remove all of the $1,000 matching amounts. After we run this UPDATE statement, the grants will all be returned to their baseline values.
UPDATE vHighValueGrants
SET Amount = Amount - 1000
The large grants are decremented by $1,000 and thus returned to their baseline values. The $1000 increase has been removed as seen in this figure below.
Suppose you accidentally ran the decrement step twice. Before we “accidentally” run the UPDATE statement again in order to create this scenario, let’s consider the amounts currently shown by the view. The smallest grant in the vHighValueGrants view is $21,000. If we rerun the UPDATE statement, this grant will become $20,000. Recall that each grant must be greater than $20,000 in order to appear in the view.
Run the UPDATE statement again and then run a SELECT statement to see all of the records in the vHighValueGrants view. The $21,000 grant (contributed by Big 6’s Foundation %) was reduced to $20,000 and thus has fallen out of the view. Now that this grant has fallen outside the criteria of vHighValueGrants, the view no longer has the ability to “see” or manipulate this record using DML statements. For the five remaining grants, you can correct their amounts and reverse the “accidental” run of the UPDATE statement by incrementing each grant by $1000. However, the only way to correct the amount of the missing grant is by running an UPDATE statement directly against the Grant table. There are only 5 large grants remaining in the view after the second $1000 decrement.
This action was clearly a mistake. We didn’t intend to remove a record from the view, but as it is currently configured, vHighValueGrants isn’t protected against these kinds of mistakes. In order to prevent data updates which would cause records to disappear from our view, we can either place a trigger on the Grant table, or we can use CHECK OPTION.
CREATE TRIGGER trg_UpdateGrant
ON dbo.[Grant]
AFTER UPDATE
AS
BEGIN
IF EXISTS( SELECT * FROM Inserted ins
INNER JOIN Deleted del
ON ins.GrantName = del.GrantName
WHERE del.Amount > 20000
AND ins.Amount <= 20000)
ROLLBACK TRAN
END
If you create the trigger and then attempt to decrement the vHighValueGrants view, you’ll find that the trigger will not allow the transaction to fall to $20,000 and thus it won’t meet the criteria of the vHighValueGrants view.
The trigger has protected our view. The transaction which attempted to reduce a large grant from $21,000 to $20,000 was forbidden and ended in the trigger.
But let’s recognize that the trigger would also prevent any existing grant from ever being changed to an amount $20,000, or lower. In other words, the trigger is so restrictive that even a DBA would be disallowed from directly updating the Grant table if the change would reduce an existing grant amount to become $20,000 or lower. The trigger is more restrictive than we intended.
Our goal was simply to restrict users from making an accidental data change through the view which would result in a grant being removed from the view. Let’s reattempt our goal by using CHECK OPTION by rebuilding the vHighValueGrants view to include CHECK OPTION.
This tells the view to disallow data changes through the view which would cause any record to fall outside of the criteria of the view. Does it work? Now attempt to decrement though the view and the CHECK OPTION will block you. Using the code below you get the following error message.
UPDATE vHighValueGrants
SET Amount = Amount – 1000
Msg 550, Level 16, State 1, Line 1
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.
The statement has been terminated.
You will however be allowed to update to the table directly.
UPDATE [Grants]
SET Amount = Amount – 1000
OK as promised here is the short definition or description of what CHECK OPTION does for views. Each time a DML statement is run against the view, CHECK OPTION validates that the resulting record set will be true to the SELECT statement which built the view. If a modification would remove a record defined by the view, then CHECK OPTION prevents the transaction from being committed.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.
Question 23
You have a table named dbo.Sales. You need to create three views from the sales table.
vSalesSeattle
vSalesBoston
vSalesSpokane
Each view will be used by each region to make changes to their rows. One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view. Which view should you create for Region1?
- CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH DIFFERENTIAL - CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION - CREATE VIEW dbo.vSalesSeattle
WITH SCHEMABINDING
AS
SELECT SalesID,OrderQty,SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1 - CREATE VIEW dbo.vSalesSeattle
WITH NOCHECK
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Hi,
The required criteria:
1. Create a view “vSalesSeattle”
2. Ensure the view does not allow improper manipulation of data through it
Having said this, we need to ensure that the view is created with the “WITH CHECK OPTION”.
Thus, option 2 is correct.
Ramakrishnan RS
Mysore, India
Correcto answer is No. 2, because we want that changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view, so we have to use WITH CHECK OPTION.
Rene Castro
El Salvador
Correct Answer : Option 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Chennai, TamilNadu, India
correct answer is Option 2. Base tables in the view cannot be modified when schema binding is specified.
Uday Bhoopalam
USA
Correct option 2)
2) CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
We need Check option to maintain the integrity pf the view condition
Leo Pius
USA
The correct option is #2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
in accordance to this blog.
Thanks a lot, it was very helpful – I didn’t know about this option.
I am from USA
Hi,
With Check Option provides a check so option 2 is correct.
OPTION 2.
Thanks
Sudhir Chawla
New Delhi, India
Hi,
Option 2 is the right Answer
“CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION”
Thanks…
Rajneesh Verma
(INDIA)
While updating the sales data to have a new LocationID, as we need to check that changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view, we are suppose to add “WITH CHECK OPTION” at the end of the lines of codes for creating a view.
So, option no. 2 is CORRECT.
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
WITH CHECK OPTION on the CREATE VIEW statement that specifies the level of checking to be done when inserting or updating data through a view. If the option is specified, every row that is inserted or updated through the view must conform to the definition of that view.
If the view is created without a WITH CHECK OPTION clause, insert and update operations that are performed on the view are not checked for conformance to the view definition.
Let me give an example which might be helpful to understand it more better.
SQL Server’s views are updateable, but it is possible that when you insert a new record or update an existing record, the record added or modified doesn’t logical belog to the view any longer. Consider the following view:
CREATE VIEW Name_View
(
SELECT * FROM Names WHERE name=’Mona’
)
Now, if you now issue the following query,
UPDATE Name_View SET name=’Lisa’
then all the records visible in the view are updated with a different name value, and therefore would disappear from the view itself, ie it wil go out of scope of the view created. This can create programming errors. You can avoid this problem by adding the WITH CHECK OPTION when you create the view.
CREATE VIEW Name_View
(
SELECT * FROM Names WHERE name=’Mona’
)
WITH CHECK OPTION
Now any insert or update operation that makes a record disappear from the view raises a runtime error.
COUNTRY – INDIA
The correct option is #2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Nikhildas
Cochin
INDIA
Answer is : Option 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Chennai,
TamilNadu,
India
Answer is option 2.
The check option prevents the access of record to other regions’ views
Kerala, India
Correct answer is option 2
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Thanks,
Prasad Yangamuni
INDIA(PUNE)
The correct option is #2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Chennai, India
Correct answer is option 2.
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Pratik Raval
India
Option 2 is corrrect
INIDA
Hi,
Option 2 is the right answer.
Apnesh Dadhich
( USA)
Ans: option 2
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Raghunath.G.R
India
Question 23
Ans :
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Chennai, INDIA
option 2
Answer: Option 2
To make modification for particular set of data we need to use where condition in view with “WITH CHECK OPTION”.
Kalpesh Patel(India)
DIFFERENTIAL : There is no such option. Hence, Eliminated.
SCHEMABINDING : Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. In our case, we are not changing the Schema of the table. Hence, Eliminated.
CHECK OPTION : Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed. Which matches our requirement.
Hence Correct Answer is no 2.
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Ishan Shah,
Gandhinagar,
India
good n congrate… :)
good and congrate. :)
Hi,
2nd Option is correct option.
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Description,
With Check Option
I am from india.
Correct Answer option No. 2
shekhar gurav.
country : India
Correct answer is option 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Here we want that Seattle sales manager should only update record of Seattle Region so that we can use WITH CHECK OPTION here. Here we already have given RegionID = 1 which is of Seattle region so this will prevent to insert or update record on this view with RegionId other than 1.
Mahmad Khoja
INDIA
Thanks Pinal , you make me fearless learning of database,
correct answer: 2)CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
kkmishra
India
The correct option is :
2) CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Reason:
CHECK OPTION tells the view to disallow data changes through the view which would cause any record to fall outside of the criteria of the view. So, option 2 is the best one & option 4 is against this.
Creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema. So, option 3 is invalid.
Regards,
Geetika Bhambri
Ahmedabad(Gujarat)-INDIA
The Correct Answer is Option -2
Thanks,
Narendra(India).
Ans: 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
with check option ensure that no record falls outside of the scope of the view.
Partha
India
Correct Answer is Option 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Country:India
Thanks,
Fazal Vahora
Correct Answer is Option #2.
New Delhi
India
Correct Ans:Option 2
# CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Regards
Rajesh
From India
Correct Answer is:
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Sudeepta,
India.
the answer is option 2)
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
the explanation for the option is, as per the definition, This tells the view to disallow data changes through the view which would cause any record to fall outside of the criteria of the view.
Explanation for all other options, that doesn’t qualify for this question is as follows:
option 1) WITH DIFFERENTIAL keyword doesn’t exist.
option 3) WITH SCHEMABINDING, Creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema.
option 4) WITH NOCHECK, Another way to bypass RI is using the WITH NOCHECK clause when adding a foreign key to a table that already has data in it.
Regards,
Ragini Gupta
India
The Correct Answer is
Option # 2
Country : India
Answer : Option 2.
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
ShaliniMeyyappan
India
ANS : 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Explanation
=========
As you have suggested in your article
Each time a DML statement is run against the view, CHECK OPTION validates that the resulting record set will be true to the SELECT statement which built the view. If a modification would remove a record defined by the view, then CHECK OPTION prevents the transaction from being committed.
Mitesh Modi
INDIA
Answer 2
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Country : India
Correct Answer : Option 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Malay
Ahmedabad, India
Hi Sir,
The correct option is option no 2)
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Since according to the question Seattle sales manager must not be able to make changes in such a way that the record falls out of the scope of the view belonging to his region.
We should place a CHECK OPTION to the view
vSalesSeattle so that he should not able to update the view so that it goes out of scope of the view.
Thanks for the knowledge.
I dint know about this.
P.Anish Shenoy,
INDIA
The correct answers is option #2
USA
Mike Michalicek
Option 2 is the only answer that includes “WITH CHECK OPTION”, so it is the correct answer.
Country: United States
Answer is #2 WITH CHECK OPTION
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Why?
the WITH CHECK OPTION ensures that the resulting record set will be true to the SELECT statement that builds the view
When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. This is not what we want
DIFFERENTIAL and NO CHECK do not appear to be valid keywords for this type of work. Differential is typically used to refer to a type of database backup.
USA
Correct answer Option :2
Correct answer Option :2
Regards,
Girish Rokade
India
Hello,
The correct option is 2.
Question :
You have a table named dbo.Sales. You need to create three views from the sales table.
vSalesSeattle
vSalesBoston
vSalesSpokane
Each view will be used by each region to make changes to their rows. One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view. Which view should you create for Region1
answer is :
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
here we have to make sure that record doesn’t go out of the control of table hence we have to use WITH CHECK OPTION when we make any modification to the vSalesSeattle view.
Diljeet Kumari
country : INDIA
The correct Answer is option 2. CREATE VIEW dbo.vSalesSeattle AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
(Sale, Nigeria)
Correct answer should be option 2.
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Rishi Divecha
ISelin, NJ, USA
Hi Pinal,
Challenge:
Question 23
You have a table named dbo.Sales. You need to create three views from the sales table.
vSalesSeattle
vSalesBoston
vSalesSpokane
Each view will be used by each region to make changes to their rows. One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view. Which view should you create for Region1?
1. CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH DIFFERENTIAL
2. CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
3. CREATE VIEW dbo.vSalesSeattle
WITH SCHEMABINDING
AS
SELECT SalesID,OrderQty,SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
4. CREATE VIEW dbo.vSalesSeattle
WITH NOCHECK
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
Correct Answer:
The correct choice is #2.
Explanation:
Choice #1 is incorrect because it uses”With Differential” which is terminology used in backups.
Choice #3 is incorrect because its use “SchemaBinding” which restrains changes to the underlying objects, not the data.
Choice #4 is incorrect because the “With NoCheck” clause is before the “AS” keyword. The “With NoCheck” clause should come after the SELECT statement at the end of the view.
Choice #2 is correct. The “With NoCheck” clause is at the end of the view definition.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
The Choice #2 is correct, the WITH CHECK OPTION provides the ability to perform checks on what is being updated.
Ramdas
NC,USA
Choice #2 is correct because it uses WITH CHECK OPTION to protect the view.
David
USA
The correct answer is option 2:
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
“WITH CHECK OPTION” is defined as “Each time a DML statement is run against the view, CHECK OPTION validates that the resulting record set will be true to the SELECT statement which built the view. If a modification would remove a record defined by the view, then CHECK OPTION prevents the transaction from being committed.” So by using that would prevent changes to the view from impacting the population of the view.
Brendan
USA
[...] Q 24) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Optio… [...]
Well
The correct answer is option 2 “CHECK OPTION”
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Because the other option doesn’t get requested result. and and follow the established criteria the select statement
schemabinding Binds the view to the schema of the underlying tables or table, – is not the case
WITH DIFFERENTIAL and NO CHECK : I couldn’t find that option in sintaxis command, I think that was a trick option
Leonardo Guerrero
From: Chile
As per my knowledge the correct answer is Option -2
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Gordon Kane
Allen TX
USA
the correct answer is option 2.
Dilip Kumar Jena
country : India
The correct option is Option 2:
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
The WITH CHECK OPTION clause prevents updates to the view which prevent the record from no longer meeting the view’s criteria. Since our view checks for RegionID = 1, if anyone tries to update a record to have a different RegionID, then the transaction will fail.
Country of Residence: USA
Correct Answer – 2
We need Check option to maintain the integrity pf the view condition
Country -> USA
Answer: Option 2
The “With Check Option” must be used as part of the view definition to ensure no data changes can be made that will cause the data to fall out of scope of the view definition. The option is added at the end of the view definition.
Country: USA
Answer should be Option # 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Ritesh (India)
Correct answer is No. 2
— Venkatesh Prabu G || Bangalore || India
The Correct Answer is :
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Explanation:
The WITH CHECK OPTION ensures that the RegionID be not changed/updated, which will make a record listed for the VIEW dbo.vSalesSeattle, to be moved out of the list.
Country:
India
Hi,
The correct option is : 2
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
As we want to ensure that the changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view
Name: Hema Chaudhry
Country: India
The correct option is option 2 i.e.
CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
india
Correct Answer is: 2
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
WITH CHECK OPTION -> During insert/update, it forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Country: India
Answer is :
2.CREATE VIEW dbo.vSalesSeattle
AS
SELECT SalesID, OrderQty, SalespersonID, RegionID
FROM dbo.Sales
WHERE RegionID = 1
WITH CHECK OPTION
Vinay
Pune, India
2nd Option is correct
WITH CHECK OPTION is a clause on the CREATE VIEW statement that specifies the level of checking to be done when inserting or updating data through a view. If the option is specified, every row that is inserted or updated through the view must conform to the definition of that view.
SANTOSHKUMAR SINGH
Mumbai, India
Option 2 is correct
WITH CHECK OPTION
Chaitanya
Hyderabad, India
A.) 3 and 5
Winner from USA: Bill Pepping
Winner from India: Ishan Shah
I thank you all for participating here. The permanent record of this update is posted on facebook page.
Option :2
From USA
Correct answer is option 2 which checks that any DML performed on the table does not invalidate the view result.
Vaishali Jain
Hyderabad, India.
[...] Q 24) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Optio… [...]
[...] SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options [...]
[...] Day 4: P.Anish Shenoy [Favorite Post] [...]
hi,
if we insert or update a view then will the base table also reflects or not?