Hi!

When we uses a search, where we use a expression for example (date >=
20040101)
The SQL-server 2000 doesn't use the indexes we have created.
Is there a problem using between values in a search?
Can I force the SQL-server to use a specific index?

Regards Jan Rockstedt

What does it do.

Do you an index on the column? if yes then Clus or NC?

If the Query would return a large proportion of the rows in the table then
it may simply choose to do a table scan.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jan" <_NO_SPAM_ (AT) telia (DOT) com> wrote

We have a NC index on the column.

The table has approximate 2,3 million rows and we only seeks a small
proportion of the rows depending on date.

Regards Jan Rockstedt


Allan Mitchell wrote:

What are you returning? If you are saying "select date from table where
(date >=20040101)" Then it should use that index.

If you are saying "select column1,column2,column3..." then it might make
more sense to not access the index just to lookup and locate the other
values.

HTH

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Jan" <_NO_SPAM_ (AT) telia (DOT) com> wrote

So WHAT do you see the query doing?

Are you a performance hit?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jan" <_NO_SPAM_ (AT) telia (DOT) com> wrote

We say "SELECT column1, column2,column3,....

FROM Table

WHERE date >= 20040101

AND date <= 20040201 "

When we do the select within a range like this, it does a table scan.

There is 2,3 million records in that table, the result will be between 500
to 2000 records.

Regards Jan Rockstedt


Ray Higdon wrote:

You could put a covering nonclustered index on this. It will still do a
table scan but a much smaller one. Some people would argue to put the
clustered index on the date column, which you could but your logical IO
would probably be about the same.

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Jan" <_NO_SPAM_ (AT) telia (DOT) com> wrote

"Jan" <_NO_SPAM_ (AT) telia (DOT) com> wrote

Do you say "date >= '20040101'" or do you say "date >= '@mydate'" ?

Depending on the context, the optimizer may not make a good decision
because it doesn't know the value of @mydate when it optimizes the
query plan. That's one possibility.

Years ago there was reported to be an issue using ">" as opposed to
">=" but as far as I know it's no longer an issue.

Yes. Try using an optimizer hint to specify the index and see if it
works better; then you can decide whether you want to leave the hint
or try to figure out why the optimizer figure it out by itself.
Despite the usual advice about not using optimizer hints, sometimes
SQL Server doesn't have enough info to do the job at the time it is
choosing its query plan so you have to help it:

SELECT * FROM table_name WITH (INDEX ( index_name ) )

JP

JP wrote:
JP,

We use "date >= '@mydate'"

Thanks for the answer. :-)

//Jan