sql-servert-sqlquery-optimizationquery-planner

Why index scan instead of seek while using comparison operator

There is a table Sales.SalesOrderDetail in database AdventureWorks2014.

I have two queries:

--Query 1 uses index IX_SalesOrderDetail_ProductID
SELECT
sod.SalesOrderID
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderID = 1

and: 

--Query 2 uses index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SELECT
sod.SalesOrderID
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderID > 1

Query plan: enter image description here

The query plan at Brentozar.com can be seen here.

And indexes:

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales]. 
[SalesOrderDetail]
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

and clustered index looks like this:

ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT 
[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

My question is why does query optimizer prefer another index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID instead of IX_SalesOrderDetail_ProductID?

Solution

  • Well, I mean, you're selecting all the rows (except maybe one). There really is no difference between a seek and a scan here. SQL Server is choosing to perform a single scan of the skinniest index instead of doing 80,000 seeks (or however many orders are in the table).

    A seek is not always the best choice, but this is a common misconception. In fact sometimes you absolutely want a scan.