Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)

In this chapter, you have learned how SQL Server stores and accesses data with and without indexes. Through analyzing query plans and I/O statistics, you have seen how important it is that the right indexes exist to optimize performance. You have learned when and how to use different index types (summarized in the table opposite) and how to maintain them.

Index Types

Clustered index

Stores the table data rows in the leaf level of the indexes. Provides fast sorted and range access based on the index keys. Only one clustered index can exist on a table.

Nonclustered index

Provides fast index seek operations based on index keys and can be created as covered indexes. Up to 249 can exist on a table.

Computed column index

Stores computed columns and provides fast access when the computed column is used as a search argument.

XML column index

Provides fast XQuery access to XML columns.

Indexed views

Stores the result of a view and provides faster access to the view. Useful when the view is queried often, especially with aggregates.

You have seen that it is important to find the right index design and that the Database Engine Tuning Advisor can be a great help in building the index design.

Chapter 6 Quick Reference

To

Do this

View an estimated query execution plan

Press Ctrl+L or choose Display Estimated Execution Plan from the Query menu.

View the actual query execution plan

Press Ctrl+M or choose Include Actual Execution Plan from the Query menu. The actual execution plan is displayed on the Execution Plan tab.

Create a clustered index

CREATE UNIQUE CLUSTERED INDEX <index_name> ON <table>(<column>)

Create a nonclustered index

CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] )

Create a primary XML index

CREATE PRIMARY XML INDEX index_name ON <object> ( xml_column_name )

Create a secondary XML index

CREATE XML INDEX index_name ON <object> ( xml_column_name ) USING XML INDEX xml_index_name FOR { VALUE | PATH | PROPERTY }

View data distribution

Query the sys.stats and sys.stats_columns views. For a specific column, use

DBCC SHOW_STATISTICS(<table>, <column>)

Retrieve fragmentation information

Use the rowset function sys.dm_db_physical_stats.

Rebuild and defragment an index

ALTER INDEX <index> ON <table>.<column> REBUILD

Use the Database Engine Tuning Advisor

In the SQL Server Management Studio, select Database Engine Tuning Advisor from the Tools menu.

Related

Категории