{"id":3816,"date":"2013-01-07T20:39:07","date_gmt":"2013-01-07T20:39:07","guid":{"rendered":"http:\/\/craftydba.com\/?p=3816"},"modified":"2013-01-08T21:00:43","modified_gmt":"2013-01-08T21:00:43","slug":"user-defined-views-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3816","title":{"rendered":"User Defined Views – Part 1"},"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.<\/p>\n

    The SQL snippet below, drops the view if it exists, creates a new stub function to demonstrate column names, and alters the function to be identical to the original example on MSDN.<\/p>\n

    -- Use the correct database\r\nUSE [AdventureWorks2012]\r\nGO\r\n\r\n-- Delete existing view\r\nIF  EXISTS (\r\n    SELECT * FROM sys.objects\r\n    WHERE object_id = OBJECT_ID(N'[HumanResources].[vHireDate]') AND [type] in (N'V')\r\n)\r\nDROP VIEW [HumanResources].[vHireDate]\r\nGO\r\n\r\n-- Create stub view (abstracting columns)\r\nCREATE VIEW [HumanResources].[vHireDate] (Eenie, Meenie, Miney, Mo)\r\nAS\r\n    SELECT 1 AS Fee, 2 AS Fi, '3' AS Fo, '4' AS Fumb\r\nGO\r\n\r\n-- Show the funny data\r\nSELECT * FROM [HumanResources].[vHireDate]\r\nGO\r\n\r\n<\/span><\/pre>\n
    I was thinking about children’s counting rhymes<\/a> and fairy tales<\/a> when I created the stub example.  <\/p>\n
    Please note, the SELECT statement uses column alias to give the constants names.  However, these aliases are overwritten by the view’s column definition.<\/p>\n
    -- Alter view to msdn example\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\nGO\r\n\r\n-- Example use of the view\r\nSELECT * \r\nFROM [HumanResources].[vHireDate] \r\nWHERE FirstName like 'J%'\r\nORDER BY FirstName\r\nGO\r\n\r\n<\/span><\/pre>\n
    The final ALTER VIEW statement creates an object that is equivalent to the one on MSDN with some literary license taken to names and formatting.  A view can be used in TSQL anywhere a table name can be supplied.<\/p>\n
    In review, a User Defined View is a compiled TSQL query based on one or more tables.  Therefore, it should run a little faster since the query plan is probably in the cache.  However, I would not use a VIEW for this reason alone.  <\/p>\n
    The major benefit of a view is data abstraction and security.  From our funny children’s example, I can abstract the underlying tables defined by the VIEW from the end user.  This allows for modifications of the underlying schema in the future.  <\/p>\n
    Next time, I will be discussing TSQL<\/a> statements that can and can not be used when creating a view.<\/p>\n
    User Defined Views – Output<\/a><\/p>\n
    User Defined Views – Example<\/a><\/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,28,29,708],"class_list":["post-3816","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-sql-server","tag-tsql","tag-user-defined-view"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3816","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=3816"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3816\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}