1

Running the following query (SQL Server 2000) the execution plan shows that it used an index seek and Profiler shows it's doing 71 reads with a duration of 0.

select top 1 id from table where name = '0010000546163' order by id desc

Contrast that with the following with uses an index scan with 8500 reads and a duration of about a second.

declare @p varchar(20)
select @p = '0010000546163'
select top 1 id from table where name = @p order by id desc

Why is the execution plan different? Is there a way to change the second method to seek?

thanks

EDIT

Table looks like 

CREATE TABLE [table] (
    [Id] [int] IDENTITY (1, 1) NOT NULL ,
    [Name] [varchar] (13) COLLATE Latin1_General_CI_AS NOT NULL)

Id is primary clustered key There is a non-unique index on Name and a unique composite index on id/name There are other columns - left them out for brevity

PaulB
  • 23,264
  • 14
  • 56
  • 75

2 Answers2

3

Now you've added the schema, please try this. SQL Server treats length differences as different data types and will convert the varchar(13) column to match the varchar(20) variable

declare @p varchar(13)

If not, what about collation coercien? Is the DB or server different to the column?

declare @p varchar(13) COLLATE Latin1_General_CI_AS NOT NULL

If not, add this before and post results

SET SHOWPLAN_TEXT ON
GO
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks so much - changing to varchar(13) did the trick. I had no idea the size of the variable could change the execution plan. Could you explain why? And thanks again. – PaulB Jul 31 '09 at 10:46
  • @PaulB: varchar(13) will be changed to varchar(20) which the index will be ignored and scan happens = slower... very late reply sorry – gbn May 04 '11 at 08:57
0

If the name column is NVARCHAR then u need your parameter to be also of the same type. It should then pick it up by index seek.

declare @p nvarchar(20)
select @p = N'0010000546163'
select top 1 id from table where name = @p order by id desc
Dimi Takis
  • 4,924
  • 3
  • 29
  • 41
  • It would convert @p to nvarchar because it has a higher precedence, not the column – gbn Jul 31 '09 at 08:57