 |
|
SQL Server Tips by Burleson |
Indexes
The concept behind indexes is to change the order of the data
(clustered index) or to add metadata (non-clustered index) for
improving the performance of queries.
Clustered indexes
- Physically stored in order (ascending or descending)
- Only one per table
- When a primary key is created a clustered index is
automatically created as well.
- If the table is under heavy data modifications or the
primary key is used for searches, a clustered index on the
primary key is recommended.
- Columns with values that will not change at all or very
seldom, are the best choices.
Non-clustered indexes
- Up to 249 nonclustered indexes are possible for each table
or indexed view.
- The clustered index keys are used for searching therefore
clustered index keys should be chosen with a minimal length.
- Covered queries (all the columns used for joining, sorting
or filtering are indexed) should be non-clustered.
- Foreign keys should be non-clustered.
If the table is under heavy data retrieval from fields other
than the primary key, one clustered index and/or one or more
non-clustered indexes should be created for the column(s) used
to retrieve the data.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
