Session3_Module5-6_Views ppt

Views / Session 3/ 1 of 40

Session 3

Module 5: Implementing Views

Module 6: Managing Views

Views / Session 3/ 2 of 40

Module 3 - Review

 Indexes are of two types, clustered and nonclustered.

 In a clustered index, data is physically sorted. Hence, a

table can have only one clustered index.

 In a nonclustered index, data is not physically sorted, only

pointers are created to point to the physical location of the

data. Hence, a table can have multiple nonclustered

indexes.

 Indexes can be created using the CREATE INDEX command.

 An index that uses more than one column to index data is

called a composite index.

 The FILLFACTOR and PAD_INDEX options reserve space on

index pages for future index expansion.

 The sp_helpindex system store procedure is used to view

index information.

Views / Session 3/ 3 of 40

Module 4 - Review

 New index features: online index operations, parallel

index operations and locking options.

 The ALTER INDEX statement is used to reorganize,

disable and rebuild an index.

 SQL Server 2005 allows non-key columns to be

included in nonclustered indexes and allows you to

create XML indexes on the XML columns in the table.

 The various methods to optimize indexes include

reorganizing, rebuilding, disabling and dropping

indexes.

 SQL Server 2005 creates index statistics that provide

information about the distribution of values in a

column or a group of columns. This information is

used by the query optimizer to speed up the query

process.

Views / Session 3/ 4 of 40

Objectives

Implementing Views