Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When I execute the following query in SQL Server 2005 it uses an index seek, as verified by viewing the execution plan.

SELECT *
FROM   Account
WHERE  Number = '123456789'

But when I run the same query, but use a parameter, it uses an index scan.

DECLARE @AccountNumber NVarChar(25)
SET @AccountNumber = '123456789'

SELECT *
FROM   Account
WHERE  Number = @AccountNumber

Since this table has over 10 million rows, the second query takes over 30 seconds while the first query takes only a few milliseconds. Do I really have to go and change all of my queries to not use parameters?

share|improve this question
    
Should this belong on stackoverflow? –  gbn Jul 22 '09 at 18:20

1 Answer 1

up vote 5 down vote accepted

There are differences between using constants and variables because, simply, SQL Server tries to optimise for the general case.

However, in this instance, my first thought is that data type precedence causing implicit conversion. What data type is the Number column?

Example: say it's varchar(25). nvarchar has higher precedence then varchar so the column is implicitly converted before comparison.

I've been bitten recently by comparing varchar vs SUSER_SNAME. And I should know better.

share|improve this answer
    
You're right, Number is VarChar(25) and when I change the variable to be a VarChar(25) it uses an index seek. Thanks! –  Stefan Moser Jul 22 '09 at 18:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.