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


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.

Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: