CODEWITHSUNDEEP
sql-serversql-server-2008
user441222
user441222

Reputation: 2001

Why there is a Index Scan?

A table structure:

AID INT (primary key)

AName varchar(50)

B table structure:

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

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

My sql query below:

    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

enter image description here

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

Upvotes: 2

Views: 63

Answers (1)

Vlad G.
Vlad G.

Reputation: 2147

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.

Upvotes: 4

Related Questions