Different Plans for the same parameter values after Implicit Conversion
Here is the test script for the queries just in case anybody is interested http://bit.ly/124g1Uy
Let me ask, what is the need to filter via a character or this was a test?
Seek Keys[1]: Start: [tempdb].[dbo].[NumbersA].NUMBER >= Scalar Operator(CONVERT_IMPLICIT(int,[@A],0)), End: [tempdb].[dbo].[NumbersA].NUMBER <= Scalar Operator(CONVERT_IMPLICIT(int,[@B],0))
uses different cardinality estimation compared to:
Seek Keys[1]: Start: [tempdb].[dbo].[NumbersA].NUMBER >= Scalar Operator((1000000)), End: [tempdb].[dbo].[NumbersA].NUMBER <= Scalar Operator((1100000))
What was unexpected was the choice of hash versus merge join. On my machine, both queries produce a serial merge join plan (with differing input estimates) but very similar runtime performance (the actual number of rows is the same, of course).
The explanation for that is that your machine has 12 processors available whereas mine only has 8. If I run the queries after pretending I have 12 processors with:
DBCC WHAT_IF(1, 12)
I get the same parallel plan for query 1 as you do. This is just a consequence of the way CPU costs are decreased for higher DOP. It turns out that DOP 12 is enough to make the parallel hash join plan appear cheaper, whereas at DOP 8 the serial merge join appears cheaper.
Using OPTION (RECOMPILE) on the first query results in the same plan as for the second query because the parameter values are sniffed and constant folded.