Utilizing compound indexes

[andrewbb]

Is it possible to force the use of a compound index in a query?

create table Test (ColOne int, ColTwo int)

The compound index is ColOne + ColTwo.

I'm interested in searching on ColTwo, but I also know the value of
ColOne will always be the number "1".

How do you structure the SQL statement to concatenate the two INTs and
use the index? Note that I don't have any control over the creation of
these indexes.

[Simon Hayes]

<an******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Is it possible to force the use of a compound index in a query?

create table Test (ColOne int, ColTwo int)

The compound index is ColOne + ColTwo.

I'm interested in searching on ColTwo, but I also know the value of
ColOne will always be the number "1".

How do you structure the SQL statement to concatenate the two INTs and
use the index? Note that I don't have any control over the creation of
these indexes.

I don't really understand what you mean - what does "concatenating INTs"
mean? Do you mean you have a computed column which is the sum of ColOne and
ColTwo? Or a computed column which does string concatenation of the values
in ColOne and ColTwo? And if ColOne is always 1, why do you even need it in
the table?

Assuming ColOne is always 1, and you search for values in ColTwo, then there
doesn't seem to be much point in indexing ColOne (unless it's part of a key
or constraint which you haven't mentioned), so I would just index ColTwo. If
you really do need a composite index on those columns, then you can change
the order of the columns in the index, and build it on (ColTwo, ColOne)
instead.

If this doesn't help, I suggest you post your CREATE INDEX or ALTER TABLE
.... ADD CONSTRAINT ... statements to make it clear exactly what you're
doing.

Simon

[Hugo Kornelis]

On 1 Apr 2005 10:40:37 -0800, an******@gmail.com wrote:

Is it possible to force the use of a compound index in a query?

create table Test (ColOne int, ColTwo int)

The compound index is ColOne + ColTwo.

I'm interested in searching on ColTwo, but I also know the value of
ColOne will always be the number "1".

How do you structure the SQL statement to concatenate the two INTs and
use the index? Note that I don't have any control over the creation of
these indexes.

Hi andrewbb,

If you can't change the index, but do want to use it, then you simply
tell SQL Server that ColOne will always be 1:

SELECT ColOne, ColTwo, ...
FROM Test
WHERE ColOne = 1
AND CopTwo = (the value you try to find)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

(an******@gmail.com) writes:

Is it possible to force the use of a compound index in a query?

create table Test (ColOne int, ColTwo int)

The compound index is ColOne + ColTwo.

I'm interested in searching on ColTwo, but I also know the value of
ColOne will always be the number "1".

How do you structure the SQL statement to concatenate the two INTs and
use the index? Note that I don't have any control over the creation of
these indexes.

As long as you say only:

SELECT ... FROM Test WHERE ColTwo = <value>

the index will be of limited use, since SQL Server would have to
scan the leaf nodes in the index. There are queries where this is a
good idea, but most often this results in a table scan.

On the other hand:

SELECT ... FROM Test WHERE ColOne = 1 AND ColTwo = <value>

is very likely to make use of the index.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp