August 8, 2001 at 10:08 am
A quick question on indices.
Let us suppose that I have a table with 2 fields used in a compound Index.
Am I right in thinking that SQL will use the compound index to perform a search on the first field in the index, but will need an additional index to be created on the 2nd column in order to speed up searches against the 2nd column?
Thanks
August 8, 2001 at 11:11 am
If your WHERE clause (or other join clause) only contains the second column, then yes, you need a separate index.
I.E.
table has ColA, ColB
index is testindex( ColA, ColB)
For
select * from table where ColA = xx and Colb=yy
the index will be used.
For
select * from table where Colb=yy
the index will not be used.
Steve Jones
August 9, 2001 at 1:34 am
Just to clarify:-
The index is on ColA, ColB.
If the WHERE clause searches on ColA only will the index be used?
August 9, 2001 at 3:16 am
I'd say yes. But why not just try it?
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
August 9, 2001 at 1:04 pm
August 9, 2001 at 3:16 pm
As long as it is not defaulting to a table scan because the index is poor, or there isnt enough data to make it worth using the index.
Andy
August 9, 2001 at 4:53 pm
It really depends on whether or not there are other columns in the table beside the two columns that are used in the index. If there are other columns in the table, SQL Server may choose to do an index scan against the index if your search condition only specifies the second column that is used in the index. For example, if your table was:
CREATE TABLE Test
(colA INT NOT NULL,
colB INT NOT NULL,
colC VARCHAR(255) NOT NULL,
...)
with an index such as:
CREATE NONCLUSTERED INDEX test_idx ON Test (colA, colB)
and you used a query such as:
SELECT colC
FROM Test
WHERE colB = some_value
SQL Server might choose to use an index scan, because the cost might be much cheaper than doing a table scan. In an index scan such as this, SQL Server would search each leaf-level page of the index looking for index entries that have the searched-for value in the second column. The cost savings could be substantial. Consider a situation where your table's data takes up 1000 pages, your index's leaf-level takes 10 pages, and your search condition will return 3 rows. The index scan would take about 13 IOs, while a table scan would take a 1000. Pretty sure bet that SQL Server will choose an index scan in such a case.
I would tend to believe, though, that if the table contained only the two columns, then the optimizer might choose to do the table scan instead of the index scan, since the cost between the two, in terms of IO, would be pretty much the same (depending on your FILLFACTOR).
Matthew Burr
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply