Conclusion

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.