1 / 9

SQL Server 2005 Ch 7

SQL Server 2005 Ch 7. Implementing Views. Creating a View. A view is a select statement that is stored on the SQL Server For querying, use a view just like using a table Can give a level of security by just allowing users access to views instead of tables. Creating a View Cont.

viho
Télécharger la présentation

SQL Server 2005 Ch 7

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. SQL Server 2005 Ch 7 Implementing Views

  2. Creating a View • A view is a select statement that is stored on the SQL Server • For querying, use a view just like using a table • Can give a level of security by just allowing users access to views instead of tables

  3. Creating a View Cont. • Command: CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [WITH CHECK OPTION]  • The AS clause contains the select statement for the view • With view attribute – selects one or more of the following: • Encryption – stores the definition of view • Schemabinding – binds all the objects that the view utilizes with the view, preventing them from being dropped until the view is dropped first • View_metadata – returns metadata about the view to client-side data access libraries • With check option - set view to be updateable on data • Views cannot utilize the following: • Compute or compute by • Into • Option • Reference any temporary table or table variable • Use order by unless TOP operator is specified

  4. Creating View Cont. • Example: USE AdventureWorks ; GO IF OBJECT_ID ('hiredate_view', 'view') IS NOT NULL DROP VIEW hiredate_view ; GO CREATE VIEW hiredate_view AS SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate FROM HumanResources.Employee e JOIN Person.Contact c on e.ContactID = c.ContactID ; GO

  5. Creating a View Cont. • Ownership Chains - the chain of ownership of objects used by a view can have a problematic affect on users if permissions are not set correctly • If multiple owners of objects must first allow select access to the objects to both the view creator and anyone utilizing the view • If all objects are owned by the view creator, you do not have to worry about ownership chains

  6. Modifying Data through Views • In modifying values in a view, SQL restricts that only values from one table can be modified • Derived columns cannot be modified such as columns derived from aggregate functions such as AVG, COUNT, SUM, MIN, and MAX or from computations ahat involves other columns or operators such as SUBSTRING • Modify view definition cannot contain Group By, Having, or Distinct • To make a view that can modify data use the WITH CHECK OPTION option • Modifiable views can use triggers

  7. Modifying data through Views Cont. Example: CREATE VIEW dbo.V_Customer AS SELECT CustomerID, CustomerName, CreditLine, AvailableCredit FROM dbo.Customer WHERE CreditLine > 1000 WITH CHECK OPTION; Example of using View: INSERT INTO dbo.V_Customer (CustomerName, CreditLine) VALUES (‘Customer2’, 300)

  8. Creating an Indexed View • SQL Server handles normal views by replacing the references in the view with actual select statements, utilizing the query optimizer. • Indexed views create another instance of the returned select statement on the database and creates a clustered index for that table that is stored for future use. As data is added the original table, it is also added to the indexed view. • Restrictions: • Many Restrictions • Go to http://msdn2.microsoft.com/en-us/library/ms191432.aspx for full list • Use create index command on view

  9. Creating Indexed View Cont. • Example • http://msdn2.microsoft.com/en-us/library/ms191432.aspx

More Related