{"id":3865,"date":"2013-01-08T18:16:35","date_gmt":"2013-01-08T18:16:35","guid":{"rendered":"http:\/\/craftydba.com\/?p=3865"},"modified":"2013-05-25T14:50:35","modified_gmt":"2013-05-25T14:50:35","slug":"user-defined-views-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3865","title":{"rendered":"User Defined Views – Part 3"},"content":{"rendered":"

\"\"<\/a>Today, I want to continue talking about database objects that are associated with stored (compiled) code.<\/p>\n

A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database.<\/p>\n

 <\/p>\n

Typical uses of a view are the following:<\/p>\n

    \n
  1. To simplify or customize the perception each user has of the database.<\/li>\n
  2. Security mechanism to grant users access to the view, not the underlying base tables.<\/li>\n
  3. To provide a backward compatible interface to emulate a table whose schema has changed.<\/li>\n<\/ol>\n

    Like most Data Definition Language (DDL<\/a>) constructs, a user defined view has three operations associated with it: CREATE VIEW<\/a>, ALTER VIEW<\/a> and DROP VIEW<\/a>.<\/p>\n

    I will be using the Adventure Works<\/a> 2012 sample database supplied by Microsoft during this talk. I will be working with the [HumanResources].[vHireDate] to demonstrate the different options a developer has when creating a view.<\/p>\n

    The first lesson is to examine the WITH CHECK OPTION<\/a> that forces all data modification statements executed against the view to follow the criteria set within SELECT statement. In short, you can not modify a record and have it disappear from the view.<\/p>\n

    The example below creates a view base on a SELECT<\/a> statement filtered by employees whose first name starts with J. It then finds Jay Adams and tries to do a legal name change to Hillary. This change fails when the view is used in a UPDATE<\/a> statement but passes when the base table is used.<\/p>\n

    --\r\n-- Lesson A - Check Option\r\n--\r\n\r\n-- Alter view to include check option\r\nALTER VIEW [HumanResources].[vHireDate] \r\nAS \r\n    SELECT \r\n        p.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n    FROM \r\n        HumanResources.Employee e \r\n        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID\r\n    WHERE \r\n        p.FirstName Like 'J%'\r\n    WITH CHECK OPTION ;\r\nGO\r\n\r\n-- Show the data\r\nSELECT * FROM [HumanResources].[vHireDate]\r\nGO\r\n\r\n-- Find Jay Adams\r\nSELECT * FROM Person.Person WHERE BusinessEntityID = 67;\r\nGO\r\n\r\n-- Legal name change (view prevents it)\r\nUPDATE [HumanResources].[vHireDate] \r\nSET FirstName = 'Hillary'\r\nWHERE BusinessEntityID = 67;\r\nGO\r\n\r\n-- Legal name change (table allows it)\r\nUPDATE Person.Person\r\nSET FirstName = 'Jay'\r\nWHERE BusinessEntityID = 67;\r\nGO\r\n\r\n<\/span><\/pre>\n
    \"\"<\/a><\/p>\n
     <\/p>\n
    The second lesson is that the definition of the view can be masked from prying eyes with the WITH ENCRYPTION<\/a> option.  I am using the sp_helptext<\/a> system stored procedure to display the view definition which works for the first call but not the second one.  <\/p>\n
    Please note, the algorithm for the masking is well know and will not stop an experienced hacker.  Check out this link a decryption routine<\/a>.<\/p>\n
    --\r\n-- Lesson B - Encryption Option\r\n--\r\n\r\n-- Get the views definition\r\nsp_helptext '[HumanResources].[vHireDate]';\r\nGO\r\n\r\n-- Alter view to include encryption option\r\nALTER VIEW [HumanResources].[vHireDate] \r\nWITH ENCRYPTION\r\nAS \r\n    SELECT \r\n        p.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n    FROM \r\n        HumanResources.Employee e \r\n        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID\r\n    WHERE \r\n        p.FirstName Like 'J%';\r\nGO\r\n\r\n-- Get the views definition\r\nsp_helptext '[HumanResources].[vHireDate]';\r\nGO\r\n\r\n<\/span><\/pre>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
     <\/p>\n
    The third lesson I want to teach today is the WITH SCHEMABINDING<\/a> option.  This option binds the view to the underlying table.  Any changes in the TABLE or COLUMN name will not be allowed once it is bound.   I am using the sp_rename<\/a> system stored procedure to attempt the object name changes.<\/p>\n
    --\r\n-- Lesson C - Schema Binding\r\n--\r\n\r\n-- Alter view to include schema binding option\r\nALTER VIEW [HumanResources].[vHireDate] \r\nWITH SCHEMABINDING\r\nAS \r\n    SELECT \r\n        p.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n    FROM \r\n        HumanResources.Employee e \r\n        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID\r\nGO\r\n\r\n-- Try to rename the column\r\nsp_rename  'Person.Person.FirstName', 'FirstName1', 'Column'\r\nGO\r\n\r\nPRINT ' ';\r\nGO\r\n\r\n-- Try to rename the table\r\nsp_rename  'Person.Person', 'Person1'\r\nGO\r\n\r\n<\/span><\/pre>\n
    \"\"<\/a><\/p>\n
     <\/p>\n
    The fourth and final lesson today is to introduce the WITH VIEW_METADATA<\/a> option.  I have been using SQL Server since version 6.5.  I have never had a good reason to use this clause.  <\/p>\n
    Taking the definition of this option right from MSDN – “Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view.”.<\/p>\n
    \r\n--\r\n-- Lesson D - View Metadata\r\n--\r\n\r\n-- Alter view to include schema binding option\r\nALTER VIEW [HumanResources].[vHireDate] (First_Name, Last_Name, BusEntity_Id, Hire_Date)\r\nWITH VIEW_METADATA\r\nAS \r\n    SELECT \r\n        p.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n    FROM \r\n        HumanResources.Employee e \r\n        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID\r\nGO\r\n\r\n-- Show the data\r\nSELECT * FROM [HumanResources].[vHireDate]\r\nGO\r\n\r\n<\/span><\/pre>\n
    \"\"<\/a><\/p>\n
     <\/p>\n
    I created a local ODBC connection<\/a> using the SQL Server client driver and a corresponding MS ACCESS linked table<\/a>.  I was not able to view the base tables before or after this option.  I think only a low level SQL API<\/a> call can access this data.  I do not know of any client tools, such as SSMS or MS ACCESS, show the base tables used in a view.<\/p>\n
    To recap this exploration, the WITH CHECK OPTION ensures that the end user will not experience disappearing records when modifying data through the view.  The WITH ENCRYPTION option will protect the definition of your views from users that have limited access rights.  <\/p>\n
    The WITH SCHEMABINDING option ensures schema changes can not occur without updating the VIEW.  If you do not use this option, sp_refreshview<\/a> should be run when changes are made to the objects underlying the view. Otherwise, the view might produce unexpected results when it is queried.  <\/p>\n
    Last but not least, I never had any real use for the WITH VIEW_METADATA option.  This option depends on whether your developers \/ users write low level API calls to MS SQL Server.<\/p>\n
    Next time, I will be exploring the rules of engagement when modifying data via a VIEW.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database.   Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[707,316,31,563,12,15,709,319,719,718,720,28,29,708,714,715,716,717],"class_list":["post-3865","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-view","tag-create-view","tag-database-developer","tag-drop-view","tag-free-code","tag-john-f-miner-iii","tag-schema-abstraction","tag-sp_helptext","tag-sp_refreshview","tag-sp_rename","tag-sql-api","tag-sql-server","tag-tsql","tag-user-defined-view","tag-with-check-option","tag-with-encryption","tag-with-schemabinding","tag-with-view_metadata"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3865","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3865"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3865\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3865"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3865"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3865"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}