Compound vs Simple Indexes

[scadreau@gmail.com]

I have two tables. I want to get one column from table B into table
A. I have added a "blank" column in table A for that information.

The "link" criteria is on 3 columns. What I am wondering is if I am
better off creating 3 different indexes (one for each column) or a
single index of all 3 columns.

Thanks,

S

[Plamen Ratchev]

Re: Compound vs Simple Indexes

It is always best to test with your data, table structures, and compare
execution plans. In general since a composite index is wider it may be
slower for maintenance and require more I/O. Also, statistics are kept only
on the first column of the index and it is really important how selective
the first column is. A single column index will fit more keys on an index
page, potentially providing faster seek.

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Erland Sommarskog]

Re: Compound vs Simple Indexes

(scadreau@gmail .com) writes:

I have two tables. I want to get one column from table B into table
A. I have added a "blank" column in table A for that information.
>
The "link" criteria is on 3 columns. What I am wondering is if I am
better off creating 3 different indexes (one for each column) or a
single index of all 3 columns.

We have far too little information to say anything with certainty.
But generally, if you have something like:

SELECT ...
FROM tbl
WHERE col1 = @val1
AND col2 = @var2
AND col3 = @var3

A compound index on (col1, col2, col3) is more efficient than three single
index, as SQL Server then can locate all matching rows directly. If there
are three single index, SQL Server may only use the index which it thinks
is the most selective, and then do lookups to filter on the other two
columns.

But depending on how your query and tables look like, not even a compound
index may be used.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx