SELECT TOP Scan the whole table

Question

If I did a command like:

SELECT TOP(10000) ProductId from ProductInfo WHERE ProductId IN
(
    SELECT ProductId from DeletedProduct
)

Will it scan the whole table or break out as soon as it finds 10000?

Answer 1

It will break out as soon as it finds 10000.

Are you not using my DELETE solution? :-)

Answer 2

I think your query would be faster if you did an inner join, like so:

SELECT TOP(10000) P.ProductId
FROM ProductInfo P INNER JOIN DeletedProduct D on P.ProductId=D.ProductId

Answer 3

For MSSQL, it will definitely index or table scan DeletedProduct since you are selecting all records there. At a guess, it will probably also do a index scan on ProductInfo unless there are >>> more than 10 000 records in it, and also unless the clustered index is on ProductId (but y, it will only read the pages needed to fill 10 000 records)

Answer 4

If you're just interested in the ProductID (rather than other columns) from ProductInfo and are using the IN just to verify that the product actually exists then something like this might be faster.

Running both your query and the below query together in management studio shows that yours has a 99% cost whereas the below has a 1% cost (much quicker); however that could just be due to the database I was using it on.

SELECT TOP(10000) ProductId
FROM DeletedProduct D
WHERE EXISTS (SELECT * FROM ProductInfo P WHERE P.ProductID=D.ProductID )