1 / 13

View

View. View ایحاد وتغییر View ایجاد ایندکس روی. USE Accounting; GO CREATE VIEW CustomerPhoneList_vw AS SELECT CustomerName, Contact, Phone FROM Customers; SELECT * FROM CustomerPhoneList_vw;. CREATE VIEW CurrentEmployees_vw AS SELECT EmployeeID , FirstName , MiddleInitial ,

yestin
Download Presentation

View

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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);

More Related