Q:

sql server logical reads are more with where clause?

SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid

(830 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5

(30 row(s) affected)
Table 'Customers'. Scan count 0, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In second query I'm limiting the rows with O.custid < 5 still it gives me logical reads 60? It should be less than 21 which shown for first query in customer table.

A:

This is too long for a comment.
You need to look at the query plans for the two queries.  SQL Server has several options for performing these queries.  For instance, here are some examples:

Scanning Customers and looking up each order in Orders using an index.
Scanning Orders table and looking up each customer in Customers.
Creating a hash table for Orders and Customers and then merging the results in the hash table.
Sorting both Customers and Orders and doing a merge join.

(and there are undoubtedly other possibilities as well).
In any case, the SQL optimizer chooses the best method for doing the join.  When you add a where clause, the best solution may be different.  This is expected, and an indication that the optimizer is doing its job.

