Compound vs Simple Indexes

[scadreau]

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]

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]

(sc******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx