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

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?

  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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

  2. 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

  3. 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

  4. correct answer is Option 2. Base tables in the view cannot be modified when schema binding is specified.

    Uday Bhoopalam
    USA

  5. 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

  6. 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

  7. 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)

  8. 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

  9. 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

  10. 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

  11. 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)

  12. 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

  13. 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

  14. Question 23

    Ans :

    CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH CHECK OPTION

    Chennai, INDIA

  15. 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)

  16. 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

  17. 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.

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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.

  24. 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

  25. Answer : Option 2.
    CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH CHECK OPTION

    ShaliniMeyyappan
    India

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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)

  32. 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

  33. 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

  34. 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

  35. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  36. 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

  37. 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

  38. 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

  39. 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

  40. 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)

  41. 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

  42. 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

  43. 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

  44. 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

  45. Correct answer is option 2 which checks that any DML performed on the table does not invalidate the view result.

    Vaishali Jain
    Hyderabad, India.

  46. Pingback: SQL SERVER – Programming and Development – Book Available for SQL Server Certification Journey to SQLAuthority

  47. Pingback: SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5 « SQL Server Journey with SQL Authority

  48. Pingback: SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198) « SQL Server Journey with SQL Authority

  49. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s