为什么会有索引扫描?

A table structure:

表结构:

AID INT (primary key)

AName varchar(50)

B table structure:

B表结构:

BID        INT (primary key)
AID        INT
BeginTime  dateTime
EndTime    datetime

There is a Non-Clustered on B table AID column.

在B表辅助列上有一个非集群。

My sql query below:

我的下面的sql查询:

    SELECT  dbo.A.AID ,
        AName
FROM    dbo.A
        INNER  JOIN dbo.B ON dbo.A.AID = dbo.B.AID
WHERE   GETDATE() BETWEEN BeginTime AND EndTime + 1

I think should be Index Seek,But why not?How to achieve?Thanks in advance!

我认为应该是索引查找，但为什么不呢?如何实现?提前谢谢!

1 个解决方案

#1

Your search condition is using BeginTime and EndTime and you need a non-clustered index on those columns to achieve a seek:

您的搜索条件是使用开始时间和结束时间，您需要在这些列上使用非聚集索引来实现查找:

CREATE NONCLUSTERED INDEX IX_1 ON B
(
    BeginTime
)
INCLUDE
(
    EndTime, 
    AID
)

The INCLUDE part is there for covering -- it allows SQL Server to satisfy the query from this non-clustered index without having to go back to the table for additional columns.

INCLUDE部分用于覆盖——它允许SQL Server满足来自非聚集索引的查询，而无需返回到表中获取其他列。