Getting a query to index seek (rather than scan)
Posted
by PaulB
on Stack Overflow
See other posts from Stack Overflow
or by PaulB
Published on 2009-07-31T08:19:12Z
Indexed on
2010/06/14
4:52 UTC
Read the original article
Hit count: 149
tsql
|sql-server-2000
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
© Stack Overflow or respective owner