Presents your 
SQL SERVER E-NEWSLETTER for September 26, 2002

<------------------------------------------->

OPTIMIZE QUERIES BY INDEXING FOREIGN KEYS

For SQL Server developers, it's good practice to index foreign key
columns within a table. Indexes on foreign keys give queries that are joining
on the foreign keys a direct path to the columns, which optimizes the
query. This is in contrast to an unknown path, which causes the query
optimizer to scan the entire table.

But if the table contains few enough rows to fit on a page within SQL
Server, the entire table will be read--eliminating the need for indexing
foreign keys. Maintaining the indexes on tables that have very few rows 
is additional overhead for SQL Server and should be avoided.

On the other hand, if the majority of the tables are large, it's
imperative to index foreign keys. Instead of scanning the entire table, the
index will allow SQL Server to select an optimized method of accessing 
the data in a more efficient method. Indexes are mechanisms that assist SQL
Server in performing at its optimal level.

However, overindexing in an Online Transaction Processing (OLTP)
environment can cause too much overhead in maintaining these indexes. 
The first step is to index the foreign keys. When there's a performance issue, 
the next step is to analyze the access paths to the data to eliminate 
indexes that aren't being used in queries.

----------------------------------------