获取查询以进行索引查找(而不是扫描) [英] Getting a query to index seek (rather than scan)

问题描述

运行以下查询 (SQL Server 2000)，执行计划显示它使用了索引查找，Profiler 显示它正在执行 71 次读取，持续时间为 0.

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

与以下相比，使用索引扫描，读取次数为 8500 次，持续时间约为 1 秒.

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?

谢谢

编辑

表格看起来像

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

Id 是主聚集键Name 上有一个非唯一索引，id/name 上有一个唯一复合索引还有其他列 - 为简洁起见省略了

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

推荐答案

现在你已经添加了架构，请试试这个.SQL Server 将长度差异视为不同的数据类型，并将转换 varchar(13) 列以匹配 varchar(20) 变量

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)

如果不是，那么collat​​ion coercien呢?DB或服务器与列不同吗?

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

这篇关于获取查询以进行索引查找(而不是扫描)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！