DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a View?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor for demonstrating the application of CREATE VIEW statement.

 

-- SQL Server CREATE VIEW statement

USE AdventureWorks2008;

GO

CREATE VIEW vProductLocation

AS

  SELECT   P.Name           AS Product,

           L.Name           AS InventoryLoc,

           SUM(PI.Quantity) AS QtyAvailable

  FROM     Production.Product AS P

           INNER JOIN Production.ProductInventory AS PI

             ON P.ProductID = PI.ProductID

           INNER JOIN Production.Location AS L

             ON PI.LocationID = L.LocationID

  GROUP BY P.Name,

           L.Name

GO

 

-- Test view with SELECT - notice ORDER BY is here not in view definition

SELECT TOP(5) * FROM vProductLocation

ORDER BY NEWID()

GO

/*    Product                       InventoryLoc            QtyAvailable

      Bearing Ball                  Miscellaneous Storage   318

      Internal Lock Washer 4        Miscellaneous Storage   603

      ML Mountain Seat/Saddle       Subassembly             72

      LL Mountain Front Wheel       Subassembly             457

      LL Mountain Frame - Black, 40 Debur and Polish        129  */

------------ 

 

-- T-SQL CREATE VIEW statement

CREATE VIEW vWorkOrder

AS

  SELECT WorkOrderID,

         P.Name                AS Product,

         OrderQty,

         CONVERT(DATE,DueDate) AS DueDate

  FROM   Production.WorkOrder W

         INNER JOIN Production.Product P

           ON W.ProductID = P.ProductID

  WHERE  P.ProductSubcategoryID IN (2,3)

GO

 

-- Test view with random SELECT

SELECT TOP(3) * FROM vWorkOrder

ORDER BY NEWID()

GO

/*    WorkOrderID Product                 OrderQty    DueDate

      34021       Road-550-W Yellow, 44   1           2003-06-04

      27710       Road-650 Black, 58      120         2003-02-15

      61003       Road-750 Black, 52      1           2004-03-28  */

------------

------------

-- View from a stored procedure result set

------------

CREATE VIEW vSPWHO

AS

SELECT * FROM OPENQUERY(YOURSERVER, 'exec sp_who')

GO

 

SELECT * FROM vSPWHO

GO

-- (131 row(s) affected)

------------

Related article:

CREATE VIEW (Transact-SQL)

http://sqlusa.com/bestpractices2005/selectfromsproc/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE