Where clause on indexed column using a variable

Question

question

Zach asked Oct 30, '09

Where clause on indexed column using a variable

I thought this might be an easy question to answer but after hours of searching the web, I'm giving up.

I have a SQL 2005 table with a few composite indexes and a few single column indexes. I've narrowed my problem down to this; if I run a query such as the following:

select * from tbl                    
where col = 'test'

the execution plan shows that index built on col is used. However if I run the query this way:

declare @var varchar(10)                    
set @var = 'test'                    
                    
select * from tbl                    
where col = @var

then the execution plan shows that the index wasn't used.

I would like to know if this is how sql works or is the sql optimizer just recognizing that it doesn't need to use the index.

Thanks in advance for your help, Z

sql-server-2005 indexes

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-03T07:14:45Z

RickD answered Nov 3, '09

Did you clear the Cache between the first and second execution? This can cause your query to be cached, even though you think you changed it, SQL will pre-compile the code and see it is the same as the cached version it has got as far as it is concerned.

If you want to do a true test, run:

DBCC FREEPROCCACHE

between the two statements.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-03T10:03:53Z

Kristen answered Nov 3, '09

I can't say I've had a problem with this in Stored Procedures, but I don't know about when using dynamic SQL, or parameterised queries (e.g. using sp_ExecuteSQL).

You might want to Google "Parameter Sniffing" though, which may enable you to force the query optimizer to take note of the actual value in your parameter.

There is also the issue that a Stored Procedure's cached Query Plan will be created the first time the Sproc runes; which may give a "sub-standard" query plan if the parameter is not very selective - e.g. a table scan instead of an index seek.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-11-06T10:54:14Z

Craig answered Nov 6, '09

Does the Col column have a definition of varchar(10)? It may be that SQL compares the column definition to the variable definition and decides that they are so dissimilar that it cannot use the index.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-11-10T12:01:59Z

Peso answered Nov 10, '09

You probably used the clustered index instead, due to the "SELECT *". SQL Server probably did this to avoid a KEY LOOKUP for you.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2009-12-03T17:21:52Z

Rajib Bahar answered Dec 3, '09

have you considered forcing index hint? you can tell the query that you want a particular index always used regardless of what SQL server decides in the background.

WITH INDEX(<INDEX_NAME>) or WTIH INDEX(<INDEX_NO>)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question