Best use of indices

  • 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?


  • If your WHERE clause (or other join clause) only contains the second column, then yes, you need a separate index.


    table has ColA, ColB

    index is testindex( ColA, ColB)


    select * from table where ColA = xx and Colb=yy

    the index will be used.


    select * from table where Colb=yy

    the index will not be used.

    Steve Jones

  • Just to clarify:-

    The index is on ColA, ColB.

    If the WHERE clause searches on ColA only will the index be used?

  • I'd say yes. But why not just try it?

    Chris Hedgate @ Apptus Technologies (

  • yes they will.

    Steve Jones

  • 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.


  • 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:


    (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