Indexes

[rushdib]

Hi,
I would like to know the performance difference on a table if an index is built using multiple columns as one index or using indexes for each columns.

Thanks,

Rushdi

 

[tlbroadbent]

That's a tough question to answer because it depends on many factors. The most important factor is the number of columns typically referenced in a WHERE clause.

Let's assume you have a table with an INDEX on three columns col1, col2, and col3. If queries always use the three columns in he WHERE clause then the compound index will be very helpful. However, if some queries use col1, some col2 and some col3, then the compound index will be most useful for the queries referencing col1. Queries referencing col2 or col3 may scan the index or the table but will not perform index seeks which are desirable for fast searches. In this 2nd scenario, three indexes, one on each column would be most useful.

SQL 2000 can use multiple indexes on a table for a single query. If the query referenced col1 and col2 in the WHERE clause and each column was indexed, SQL Server 2000 may be able to use both indexes in the search. It may not use both indexes if it is not advantageous to do so.

Other factors to consider are the number of rows in the table, the distribution of data, frequency of updates... I find the best way to create good indexes is to test various indexes.

There are some good references you can read. I've listed several on a SQL Performance web page I created. However, I haven't updated it recently so there may be better resources available. You can check it out if you want. It contains links to some informative pages on index tuning.

http://tlbroadbent.home.attbi.com/sql/ss_performance.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm