Getting a query to index seek (rather than scan)

Question

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

Answer 1

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

Answer 2

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