3

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?

weegee
  • 143
  • 7
Stefan Moser
  • 177
  • 1
  • 9

1 Answers1

7

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.

gbn
  • 6,079
  • 1
  • 18
  • 21
  • 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