Why this Statement Show Index Scan in Exec Plan?

Question

I have two statements that return the same result, but produce different execution plans.

• First >>> Index Seek
• Second >>> Index Scan

Can anyone explain why?

Example

CREATE TABLE OrderDetails (intOrderId int, intItemId int, dtOrderDate Datetime, intQty int, intPrice int, intDiscount int)
GO

CREATE CLUSTERED INDEX CI_OrderId ON OrderDetails(intOrderId)
GO

CREATE NONCLUSTERED INDEX NCI_ItemId ON OrderDetails(intItemId)
GO

-- Populate Data
SET NOCOUNT ON

DECLARE @i int
SET @i =10
WHILE @i < 100000
BEGIN
     INSERT INTO OrderDetails
     VALUES (@i, round(rand()*9999,0)+1, getdate() - round(rand()*999,0), round(rand()*99,0)+1, round(rand()*9999,0)+1, round(rand()*99,0)+1)

     SET @i = @i + 1
END
GO

-- Check Execution Plans

-- NCI SEEK
SELECT intOrderId, intItemId 
FROM   OrderDetails
WHERE  intItemId = 600 * 10

-- NCI SCAN
SELECT intOrderId, intItemId 
FROM   OrderDetails
WHERE  intItemId/10 = 600

Answer 1

There are two reasons...

1. Because SQL Server doesn't algebraically manipulate your WHERE clause.
2. Because there are 10 values that match your second query

What I mean by the first comment is that the optimiser can see that you're doing something to the intItemId, but it doesn't try to find a short-cut. Instead it has to perform the /10 on every row to see what the result is.

(It doesn't know that 6000/10 = 600 but that 5999 doesn't. So it tries it on every row.)

That links in to the second reason. Your question implies that you expect only row intItemId = 6000 to be returned by your second query. But 6001/10 through to 6009/10 all equal 600 too, due to integer arithmetic. So, the algebraic manipulation of x/10=y becoming x=y*10 isn't valid when you do integer arithmetic. Which is part of the reason the optimiser doesn't try.

In short: Whenever you manipulate the indexed field in virtually any way, you'll prevent the use of the index and get a scan rather than a seek.