SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options – Day 24 of 35

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Easy Introduction to CHECK Options - Day 24 of 35 joes2pros4 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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Easy Introduction to CHECK Options - Day 24 of 35 j2p_24_1

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Easy Introduction to CHECK Options - Day 24 of 35 j2p_24_2

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Easy Introduction to CHECK Options - Day 24 of 35 j2p_24_3

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Easy Introduction to CHECK Options - Day 24 of 35 j2p_24_4

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Easy Introduction to CHECK Options - Day 24 of 35 j2p_24_5

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?

  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

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 which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Parameters – Day 25 of 35

Related Posts

77 Comments. Leave new

  • Mike Michalicek
    August 24, 2011 5:39 pm

    The correct answers is option #2

    USA

    Mike Michalicek

    Reply
  • Option 2 is the only answer that includes “WITH CHECK OPTION”, so it is the correct answer.

    Country: United States

    Reply
  • 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

    Reply
  • Correct answer Option :2

    Reply
  • Correct answer Option :2

    Regards,
    Girish Rokade
    India

    Reply
  • Diljeet Kumari
    August 24, 2011 7:56 pm

    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

    Reply
  • 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)

    Reply
  • 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

    Reply
  • 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

    Reply
  • The Choice #2 is correct, the WITH CHECK OPTION provides the ability to perform checks on what is being updated.

    Ramdas
    NC,USA

    Reply
  • Choice #2 is correct because it uses WITH CHECK OPTION to protect the view.

    David
    USA

    Reply
  • 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

    Reply
  • Leonardo Guerrero (@Cibek)
    August 24, 2011 9:41 pm

    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

    Reply
  • sumitkumarverma
    August 24, 2011 10:10 pm

    As per my knowledge the correct answer is Option -2

    Reply
  • 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

    Reply
  • dilipkumarjena
    August 25, 2011 12:09 am

    the correct answer is option 2.

    Dilip Kumar Jena
    country : India

    Reply
  • 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

    Reply
  • A. Arul Prakash
    August 25, 2011 2:21 am

    Correct Answer – 2

    We need Check option to maintain the integrity pf the view condition

    Country -> USA

    Reply
  • 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

    Reply
  • 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)

    Reply

Leave a Reply