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

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'

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?

  • Should this belong on stackoverflow? – gbn Jul 22 '09 at 18:20

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.

  • 1
    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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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