/****************************************************** * * Name: exploring-views-part2.sql * * Design Phase: * Author: John Miner * Date: 01-06-2013 * Blog: www.craftydba.com * * Purpose: Create a series of articles on * database developer (70-433) topics. * * Topic: Talk about views - part 2. * ******************************************************/ -- Use the correct database USE [AdventureWorks2012] GO -- -- Lesson A - use top 100 with order by -- -- Alter view, order by clause ALTER VIEW [HumanResources].[vHireDate] AS SELECT TOP 100 p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ORDER BY p.FirstName; GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO -- -- Lesson B - Certain constructs not allowed with views. -- -- Save employee table as local temp table SELECT * INTO #tmpEmployee FROM HumanResources.Employee; GO -- Alter view, [into], [option], [#tmp] are syntax errors ALTER VIEW [HumanResources].[vHireDate] AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate -- INTO #tmpBadIdea FROM -- #tmpEmployee e HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID -- OPTION (MERGE JOIN); GO -- -- Lesson C - Use union to join result sets -- -- Alter view, just people whos names start with J or D ALTER VIEW [HumanResources].[vHireDate] AS -- J names SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE P.FirstName LIKE 'J%' UNION ALL -- D names SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE P.FirstName LIKE 'D%' GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO -- Restore the definition ALTER VIEW [HumanResources].[vHireDate] AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID; GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO