I have 2 queries

SELECT * FROM Table1

JOIN Table2

ON Table1.Column1 = Table2.Column1

SELECT * FROM Table1

JOIN Table2

ON Table1.Column1 = Table2.Column1

AND Table1.Column2 = XXX

Both these query returns same number of results say 7000. And Table1 is a partitioned table with Column2 is the partition key. When i compared the execution plan of these 2 queries, the relative cost of these 2 queries is 65:35 which shows 2nd query is around 40% better than 1st query in terms of performance. But my concern is with the IO statistics.

The IO statistics of these two queries are as follows.

1st Query

---------

Table1 - Scan Count:0 Logical Reads:100000Physical Reads:200

2nd Query

---------

Table1 - Scan Count:7000 Logical Reads:80000Physical Reads:100

There has been a decrease in the no of logical and physical reads. But the scan count has increased to the number of rows returned by the query. I have the following questions on this.

1. Which is the better query in terms of IO?

2. What does Scan Count exactly mean. And howz it related to IO.?

3. As per the Scan count defenition its a table or index scan. If its a table or index scan howz it better in performance. (There are no scans happening on this table in the execution plan)?

4. Is there a better way to compare the IO of queries?

5. Is there a way to get the IO in terms of size (like MBs) other than the no of reads?