470,636 Members | 1,439 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

Utilizing compound indexes

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.

Jul 23 '05 #1
3 1479

<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
Jul 23 '05 #2
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)
Jul 23 '05 #3
(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
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by robert | last post: by
4 posts views Thread by Sonia | last post: by
6 posts views Thread by David W. Fenton | last post: by
6 posts views Thread by William Ahern | last post: by
7 posts views Thread by Eric Laberge | last post: by
2 posts views Thread by scadreau | last post: by
27 posts views Thread by Nate Eldredge | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.