SQL Refuses to use index when argument = @variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Refuses to use index when argument = @variable

Author

Topic

rscgeorge
Starting Member

2 Posts

Posted - 2003-02-14 : 05:40:00

I have 2 queries, each within a single batch:

Query 1:

select *
from table1
where status = 4

Query 2:

declare @intstatus
set @intstatus = 4
select *
from table1
where status = @intstatus

There is a non-clustered index on column 'status'.
Both queries return the same results but only query 1 uses the index.

Any ideas why ?

Richard

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-02-14 : 07:45:59

only thing i can think of...is that the "declare @intstatus" statement doesn't give a datatype....and thus by default may be a variant.

the query processor then would have to do an implicit conversion for the query to work and this conversion requirement may be a trigger that rules out use of an index!

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-14 : 08:47:35

Maybe selectivity is not good enough for optimazer to use it.
Run update statistics on that table.
You can get more information about the table/indexes running dbcc showcontig('table1').
You can also force index:
select ...
from table1 with (index=index_name)

rscgeorge
Starting Member

2 Posts

Posted - 2003-02-17 : 03:31:57

I have declared @intg as integer (as is the status column data type) and the index selectivity is very high (2 rows returned out of 1000000). I have run UPDATE STATISTICS on the table (although this is irrelevant since both queries would not use the index if this was to blame).

I cannot use WITH INDEX = ... because the where clause is larger than this on the production query and other indexes may be more suitable for different parameters.

I am at a loss to explain - any more ideas ?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-18 : 10:56:04

It sounds as if your actual query is more than you posted. Can you post the actual code and possibly what the structures look like?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources