1 / 6

Views

Views. Views CREATE VIEW AS Uses for Views. Views. Views are definitions of queries that can be treated like tables Only the view definition is stored

srendon
Download Presentation

Views

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. Views • Views • CREATE VIEW AS • Uses for Views

  2. Views • Views are definitions of queries that can be treated like tables • Only the view definition is stored • When the view is called the definition is applied against current data in the database and that data is fed into the query that calls the view • Views have several advantages • Serve as a security measure • Simplify complicated queries • Perform tasks difficult to perform in a query • Encapsulate frequently used query segments for reuse

  3. CREATE VIEW • Use a CREATE VIEW Statement to create a view CREATE VIEW vw_SuppliersWithProducts AS SELECT Suppliers.SupplierID, CompanyName, Country, ProductID, ProductName FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID SELECT * FROM vw_SuppliersWithProducts

  4. Using Views for Special Operations • Remember this UNION query? • How can we get the results sorted by CompanyName? SELECT CAST(SupplierID AS char(10)) AS 'ID', CompanyName, 'Supplier' AS 'Type' FROM Suppliers UNION SELECT CAST(CustomerID AS char(10)) AS 'ID', CompanyName, 'Customer' AS 'Type' FROM Customers

  5. Using Views for Special Operations (cont.) • Define thequery as a View • Query theview with ORDER BY CREATE VIEW vw_Companies AS SELECT CAST(SupplierID AS char(10)) AS 'ID', CompanyName, 'Supplier' AS 'Type' FROM Suppliers UNION SELECT CAST(CustomerID AS char(10)) AS 'ID', CompanyName, 'Customer' AS 'Type' FROM Customers SELECT * FROM vw_Companies ORDER BY CompanyName

  6. Managing Views • CREATE VIEW creates the view as a permanent object in the DB • You can delete it from the Views tree node or useDROP VIEW viewname • You cannot create two views with the same name • ALTER VIEW viewname followed by a view definition will replace the old view with the new definition

More Related