130 likes | 343 Views
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 ,
E N D
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, • LastName, • Title, • HireDate, • ManagerEmpID, • Department • FROM Employees • WHERE TerminationDate IS NULL;
USE AdventureWorks2008 • GO • CREATE VIEW CustomerOrders_vw • AS • SELECT sc.AccountNumber, • 305 • Chapter 10: Views • soh.SalesOrderID, • soh.OrderDate, • sod.ProductID,
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);
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;
CREATE VIEW [<schema name>].<view name> [(<column name list>)] • [WITH [ENCRYPTION] [[,] SCHEMABINDING] [[,] VIEW_METADATA]] • AS • <SELECT statement> • [WITH CHECK OPTION][;]
ALTER VIEW CustomerOrders_vw • WITH ENCRYPTION • AS • SELECT sc.AccountNumber, • soh.SalesOrderID, • soh.OrderDate, • sod.ProductID, • pp.Name, • sod.OrderQty, • sod.UnitPrice,
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;
EXEC sp_helptext CustomerOrders_vw • The text for object ‘CustomerOrders_vw’ is encrypted.
براي ايجاد ايندکسView آماده سازي • ALTER VIEW CustomerOrders_vw • WITH SCHEMABINDING • AS • SELECT sc.AccountNumber, • soh.SalesOrderID, • soh.OrderDate, • sod.ProductID, • pp.Name, • sod.OrderQty, • sod.UnitPrice,
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;
CREATE UNIQUE CLUSTERED INDEX ivCustomerOrders • ON CustomerOrders_vw(AccountNumber, SalesOrderID, ProductID);