Hi,

In order to decide which is the 'better' query, we would need to know what defines 'best' for you:

1. Logical IO

2. CPU

3. Concurrency

4. Response time

5. Overall server throughput

4. Query plan coolness

5. ...and so on

I would imagine that the second query is prettier as an execution plan. The equality predicate on the partitioning column should mean that the query optimizer (QO) just searches the one partition instead of the whole table.

It may then need to scan (either a full partition scan, or a range scan) to satisfy the join condition. It sounds (from the scan count) as if a loop join is being used.

One would think that the QO might prefer a HASH or MERGE join here - try forcing it with a hint and check out the effects.

The first query does 20%-odd more logical IO, so in that narrow sense it is less efficient.

It's difficult to go into heaps of detail without seeing the actual execution plan - so if you can attach those that'd be great.

BTW a range scan is a very efficient way of grabbing a load of rows all at once (the scan range can be small or large) and will usually outperform the equivalent number of key/rid lookups.

Cheers,