2779B Module 7 - Implementing Views

A view is a permanent select statement.

Three kinds

-standard

Combine data from one or more tables or views.

-indexed

The view persists by creating a unique clustered index on the view

-partitioned

Joins partitioned data from the base tables across one or more servers

Ownership chains

Encryption option

Modifying data via views

Creating a view

-use the GUI or TSQL

CREATE VIEW <schemaname>.<viewname>

-schema binding

View restrictions:

-cant be nested more than 32 levels deep

-Cant have more than 1024 columns

-cant use COMPUTE, COMPUTE BY, or INTO on a view

-cant use ORDERBY without using TOP ß in the creation of the view

-You cant apply an after trigger to a view but you can apply an instead of trigger to it.

Ownership chains

The same user has to own all the tables referencesd in a view in order for the view to not check the credentials of the person queriying the view.

So if bob owns employee.info and sally owns employee.job and the two tables are joined in a view, when sarah queries the view she will get an error. (Unless bob and Sally both have sysadmin)

Play with this

How to get Information about Views

-SSMS

-TSQL

-sys.views returns list of views in the DB

-sp_helptext returns definitions of non-encrypted views

-sys.sql_dependencies returns objects (including views) that depend on other objects

Encryption

-Option on CREATE VIEW Transact-SQL statement

-encrypts view definition in sys.syscomments table

-protects view logic

If you do this even you as the owner can not see the definition of the view.

Syntax:

CREATE VIEW [HR].[vEmp]

WITH ENCRYPTION AS SELECT …

If you use the ALTER VIEW command and don’t specify “WITH ENCRYPTION” the view will no longer be encrypted.

Modifying data via View

-Views do not maintain data (except indexed views)

-Updates to views update base tables.

-Requirements

-Updates cant affect more than one base table

-Updates cant modify columns derived from aggregate functions or calculations

-updates cant modify columns affected by GROUP BY,HAVING, or DISTINCT clauses.

-Updates to views are restricted by using the WITH CHECK OPTION (rows cant be modified in a way that would make them disappear from the view)

WITH CHECK OPTION (books online):

CHECK OPTION

Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

Note: Any updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.

The following example shows a view named SeattleOnly that references five tables and allows for data modifications to apply only to employees who live in Seattle.

Optimizing performance

Considerations

-views as resolved dynamically consuming overhead.

-Nested views consume more overhead

-review definition of unencrypted nested views

-SQL Server Profiler tests performance.

-Indexed views and partitioned views can improve performance

Indexed Views

-Indexed views have a unique clustered index

-Materializes view (view data is stored in leaf level pages)

-Allows query optimizer to use view in query resolution

-Use when:

-Performance gains are more important than maintenance overhead

-queries perform a lot of joins and aggregations

-The first index on a view must be a clustered index.

Syntax:

CREATE UNIQUE CLUSTERED INDEX [IX_IndexName]

ON [SCHEMANAME.TABLENAME]

( [FIELD1NAME] ASC ,[FIELD2NAME] ASC)

Restrictions:

-You must have an unbroken ownership chain.

-View must reference tables only in the same database.

-The view must be defined by using the SCHEMABINDING option.

Partitioned Views:

-Joining tables from different servers.

-Can cause latency, view may break of server isn’t avail.