View

1. View View ایحاد وتغییر Viewایجاد ایندکس روی

2. USE Accounting; • GO • CREATE VIEW CustomerPhoneList_vw • AS • SELECT CustomerName, Contact, Phone • FROM Customers; • SELECT * FROM CustomerPhoneList_vw;

3. CREATE VIEW CurrentEmployees_vw AS • SELECT EmployeeID, • FirstName, • MiddleInitial, • LastName, • Title, • HireDate, • ManagerEmpID, • Department • FROM Employees • WHERE TerminationDate IS NULL;

4. USE AdventureWorks2008 • GO • CREATE VIEW CustomerOrders_vw • AS • SELECT sc.AccountNumber, • 305 • Chapter 10: Views • soh.SalesOrderID, • soh.OrderDate, • sod.ProductID,

5. sod.LineTotal • FROM Sales.Customer AS sc • INNER JOIN Sales.SalesOrderHeader AS soh • ON sc.CustomerID = soh.CustomerID • INNER JOIN Sales.SalesOrderDetail AS sod • ON soh.SalesOrderID = sod.SalesOrderID • INNER JOIN Production.Product AS pp • ON sod.ProductID = pp.ProductID • WHERE CAST(soh.OrderDate AS Date) = • CAST(DATEADD(day,-1,GETDATE()) AS Date);

6. CREATE VIEW PortlandAreaAddresses_vw AS • SELECT AddressID, • AddressLine1, • City, • StateProvinceID, • PostalCode, • ModifiedDate • FROM Person.Address • WHERE PostalCode LIKE ‘970%’ • OR PostalCode LIKE ‘971%’ • OR PostalCode LIKE ‘972%’ • OR PostalCode LIKE ‘986[6-9]%’ • WITH CHECK OPTION;

7. CREATE VIEW [<schema name>].<view name> [(<column name list>)] • [WITH [ENCRYPTION] [[,] SCHEMABINDING] [[,] VIEW_METADATA]] • AS • <SELECT statement> • [WITH CHECK OPTION][;]

8. ALTER VIEW CustomerOrders_vw • WITH ENCRYPTION • AS • SELECT sc.AccountNumber, • soh.SalesOrderID, • soh.OrderDate, • sod.ProductID, • pp.Name, • sod.OrderQty, • sod.UnitPrice,

9. sod.UnitPriceDiscount * sod.UnitPrice * sod.OrderQty AS TotalDiscount, • sod.LineTotal • FROM Sales.Customer AS sc • INNER JOIN Sales.SalesOrderHeader AS soh • ON sc.CustomerID = soh.CustomerID • INNER JOIN Sales.SalesOrderDetail AS sod • ON soh.SalesOrderID = sod.SalesOrderID • INNER JOIN Production.Product AS pp • ON sod.ProductID = pp.ProductID;

10. EXEC sp_helptext CustomerOrders_vw • The text for object ‘CustomerOrders_vw’ is encrypted.

11. براي ايجاد ايندکسView آماده سازي • ALTER VIEW CustomerOrders_vw • WITH SCHEMABINDING • AS • SELECT sc.AccountNumber, • soh.SalesOrderID, • soh.OrderDate, • sod.ProductID, • pp.Name, • sod.OrderQty, • sod.UnitPrice,

12. sod.UnitPriceDiscount * sod.UnitPrice * sod.OrderQty AS TotalDiscount, • sod.LineTotal • FROM Sales.Customer AS sc • INNER JOIN Sales.SalesOrderHeader AS soh • ON sc.CustomerID = soh.CustomerID • INNER JOIN Sales.SalesOrderDetail AS sod • ON soh.SalesOrderID = sod.SalesOrderID • INNER JOIN Production.Product AS pp • ON sod.ProductID = pp.ProductID;

13. CREATE UNIQUE CLUSTERED INDEX ivCustomerOrders • ON CustomerOrders_vw(AccountNumber, SalesOrderID, ProductID);