{"id":3840,"date":"2013-01-07T21:48:10","date_gmt":"2013-01-07T21:48:10","guid":{"rendered":"http:\/\/craftydba.com\/?p=3840"},"modified":"2013-01-08T21:00:57","modified_gmt":"2013-01-08T21:00:57","slug":"user-defined-views-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3840","title":{"rendered":"User Defined Views – Part 2"},"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 TSQL snippet drops the [HumanResources].[vHireDate] view if it exists. <\/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<\/span><\/pre>\n
    The first lesson is that views are based upon a single simple or complex TSQL statement and to use the ORDER BY<\/a> clause in a view, we need to specify a TOP<\/a> 100 filter.  Otherwise, the algebraic parser will stop the statement execution with an error.<\/p>\n
    --\r\n-- Lesson A - use top 100 with order by\r\n--\r\n\r\n-- Alter view, order by clause\r\nCREATE VIEW [HumanResources].[vHireDate] \r\nAS \r\n    SELECT \r\n\tTOP 100 \r\n\tp.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n    FROM \r\n\tHumanResources.Employee e \r\n        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID\r\n    ORDER BY\r\n\tp.FirstName;\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
    The second lesson is that view can not contain the following:  reference to temporary tables<\/a>, the INTO<\/a> clause for table creation or the OPTION <\/a>clause for query hints.  The example below tries to break all three restrictions.  This results in a error produced by the algebraic parser.<\/p>\n
    --\r\n-- Lesson B - Certain constructs not allowed with views.\r\n--\r\n\r\n-- Save employee table as local temp table\r\nSELECT * INTO #tmpEmployee FROM HumanResources.Employee;\r\nGO\r\n\r\n-- Alter view, [into], [option], [#tmp] are syntax errors\r\nALTER VIEW [HumanResources].[vHireDate] \r\nAS \r\n    SELECT \t   \r\n        p.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n--  INTO #tmpBadIdea\r\n    FROM \r\n--      #tmpEmployee e\r\n        HumanResources.Employee e \r\n        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID\r\n--\tOPTION (MERGE JOIN);\r\nGO\r\n\r\n<\/span><\/pre>\n
    \"\"<\/a><\/p>\n
    The third lesson is that the UNION<\/a> key word should be used to join multiple result sets.  The example below returns all people who’s first name starts with D or J.<\/p>\n
    --\r\n-- Lesson C - Use union to join result sets\r\n--\r\n\r\n-- Alter view, just people whos names start with D or J\r\nALTER VIEW [HumanResources].[vHireDate] \r\nAS \r\n    -- J names\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\r\n    UNION ALL\r\n\r\n    -- D names\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 'D%'\r\nGO\r\n\r\n\r\n-- Show the data\r\nSELECT * FROM [HumanResources].[vHireDate]\r\nGO\r\n\r\n<\/span><\/pre>\n
    Last but not least, we should restore the user defined view before leaving for the day.<\/p>\n
    -- Restore the definition\r\nALTER VIEW [HumanResources].[vHireDate] \r\nAS \r\n    SELECT \r\n\tp.FirstName\r\n      , p.LastName\r\n      , e.BusinessEntityID\r\n      , e.HireDate\r\n    FROM \r\n\tHumanResources.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
    To recap today’s talk, views can be defined by one simple or complex TSQL statement.  To join result sets in the view, use the UNION key word.  Certain keywords and objects are restricted from usage when defining a view.  Please look at the Books On Line (BOL) or MSDN articles for more information.<\/p>\n
    Next time, I will be discussing options that can used when creating a view.  For each option, I will show an example that will demonstrate why to use it in the first place.<\/p>\n
    Views – Union All Output<\/a><\/p>\n
    Views – Do’s and Don’ts Examples<\/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,712,15,711,40,709,28,713,710,29,88,708],"class_list":["post-3840","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-into","tag-john-f-miner-iii","tag-option","tag-order-by","tag-schema-abstraction","tag-sql-server","tag-temporary-tables","tag-top-100","tag-tsql","tag-union","tag-user-defined-view"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3840","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=3840"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3840\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3840"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}