sql server views
TRANSCRIPT
ViewsSimilar to an Access query
Predefined SELECT statementAt most basic level specifies which columns
and rows to returnProvides a virtual table
SELECT * FROM Customers represents rows and columns but isn’t for storing data
Reasons for ViewsReduce database complexity‘Hide’ sensitive dataImprove query performance
Reduce ComplexityProvide access to data from multiple tables
Normalization doesn’t get in the wayLimit number of columns returned
Instead of returning all columns just return ones commonly used
Hiding DataProvide only columns that are appropriateProvide only rows that are appropriateCan have multiple views on one table with
different fields and criteria for different user groups
Can also hide table and database organization
Ease of UseCan provide access to current information
List only students who are currently enrolled in a course
Can organize data for common reportsList students with assignments for a section
View OptionsEncryption prevents viewing the SQL
statementSchema binding ties the view to objects it
depends onCan’t drop a table – or column – that’s
referenced in a bound viewCan sort results
Requires use of TOP optionCan also use ORDER BY in SELECT where
view is used
PerformanceBy default views run just as a query
processed from command lineAdds second step to execution:
select from view execute view itself
Can add an index to speed execution
Indexed ViewsSpeeds up execution of query, but has rules:
View must be schema-boundView can’t reference other views (only tables &
functions)Two-part names required, and must be same
owner as view (dbo.Students)
Indexed Views (cont.)View and source tables must be in same
databaseFunctions used must be deterministicANSI_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
DeterministicAn indexed view must be deterministic
Result of calculation is the same with same inputs
DateAdd is deterministicGetDate is non-deterministic
Places to Consider An IndexJoins 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
Indexes’ DownsideHave another list that must be maintained
when data changesCan’t reference other viewsTables must be in same databaseCannot 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
Updateable ViewsCan 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.
Updating rows using a viewUse the UPDATE statement to update a table
through a viewUse the view name in the UPDATE clauseThe view must be updatable (prior slide)The UPDATE statement can’t update data in
more than one table.
WITH CHECKIf 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