SQL Server Views

1. Views
Similar to an Access query
Predefined SELECT statement
At most basic level specifies which columns and rows to
return
Provides a virtual table
SELECT * FROM Customers represents rows and
columns but isn’t for storing data

2. Reasons for Views
Reduce database complexity
‘Hide’ sensitive data
Improve query performance

3. Reduce Complexity
Provide access to data from multiple tables
Normalization doesn’t get in the way
Limit number of columns returned
Instead of returning all columns just return ones
commonly used

4. Hiding Data
Provide only columns that are appropriate
Provide only rows that are appropriate
Can have multiple views on one table with different
fields and criteria for different user groups
Can also hide table and database organization

5. Ease of Use
Can provide access to current information
List only students who are currently enrolled in a course
Can organize data for common reports
List students with assignments for a section

6. View Options
Encryption prevents viewing the SQL statement
Schema binding ties the view to objects it depends on
Can’t drop a table – or column – that’s referenced in a
bound view
Can sort results
Requires use of TOP option
Can also use ORDER BY in SELECT where view is used

7. Performance
By default views run just as a query processed from
command line
Adds second step to execution:
select from view
execute view itself
Can add an index to speed execution

8. Indexed Views
Speeds up execution of query, but has rules:
View must be schema-bound
View can’t reference other views (only tables &
functions)
Two-part names required, and must be same owner as
view (dbo.Students)

9. Indexed Views (cont.)
View and source tables must be in same database
Functions used must be deterministic
ANSI_NULLS and QUOTED_IDENTIFIERS must be on
when view and source tables created
ANSI_NULL – Set to on requires use of IS to compare to
NULL
QUOTED_IDENTIFIERS – Set to on indicates that double
quotes identify object names

10. Deterministic
An indexed view must be deterministic
Result of calculation is the same with same inputs
DateAdd is deterministic
GetDate is non-deterministic

11. Places to Consider An Index
Joins and aggregations of large tables
Repeated patterns of queries (common WHERE
clause)
Repeated aggregations on the same or overlapping
sets of columns
Repeated joins of the same tables on the same keys

12. Indexes’ Downside
Have another list that must be maintained when data
changes
Can’t reference other views
Tables must be in same database
Cannot sort view in definition
Can use ORDER BY when view referenced in SELECT
Unique clustered index must be created before any
other indexes can be created

13. Updateable Views
Can update base tables through a view.
To have an updateable view,
Can’t include a DISTINCT or TOP clause.
Field list can’t include an aggregate function.
Field list can’t include a calculated value.
Can’t include a GROUP BY or HAVING clause.
Can’t include the UNION operator.

14. Updating rows using a view
Use the UPDATE statement to update a table through
a view
Use the view name in the UPDATE clause
The view must be updatable (prior slide)
The UPDATE statement can’t update data in more than
one table.

15. WITH CHECK
If WITH CHECK is used when the view is created,
trying to change a row such that it wouldn’t be
included in the view result will result in an error.
If a view only returns rows where city = ‘Olympia’, and
city is changed to ‘Lacey’ an error occurs