Unit 7Unit 7

Indexes and ViewsIndexes and Views

Key ConceptsKey Concepts• Index structure• Clustered vs. non-clustered indexes• Query optimization• Creating indexes• Selecting index columns• Index forms• Creating views• Using views• Updating through views

Data StorageData Storage• Introduction to SQL, Figure 20.1, pg.

580

Indexed DataIndexed Data• Introduction to SQL, Figure 20.2, pg.

582

IndexesIndexes• Based on one or more columns.• Stored as physical database items.• When indexed table columns are updated,

indexes must also be updated.• Unique indexes are used to force

uniqueness on index columns.– Single-column index, each value must be

unique– Multiple-column index, the combined

column value for each row must be unique

Clustered and Non-Clustered and Non-Clustered IndexesClustered Indexes

• Clustered index– Table is physically sorted in index order– A table can have only one clustered index

• Non-clustered index– Logically organizes data– Does NOT change the data's physical

storage– A table can have multiple non-clustered

indexes

CREATE INDEXCREATE INDEX• Introduction to SQL, Example 20.7

and 20.9 on pg.591

Index ColumnsIndex Columns• Commonly used as selection criteria• High cardinality• Enforced uniqueness• Foreign key constraints

Impact of CardinalityImpact of Cardinality• Introduction to SQL, Figure 20.4, pg.

599

Processing SpeedProcessing Speed• Introduction to SQL, Figure 20.5, pg

600

Index FormsIndex Forms• Multitable (join) index• Selective index• Hash index• Bitmap index

View FundamentalsView Fundamentals• Virtual representation of one or more

tables.• Used like a table when retrieving

data.• Specify the columns available to

users or applications.• Hide secure columns from users or

applications.• Can include computed columns.

Creating ViewsCreating Views• Introduction to SQL, Examples 21.1

and 21.2, pg. 612

Update RestrictionsUpdate Restrictions• SELECT statement cannot contain:

– DISTINCT– Aggregate functions– GROUP BY– HAVING– ORDER BY

• Computed (virtual) columns cannot be updated.