Eugene-928407 (5/25/2012)
The odd thing is that the 1st query runs much slower. According to the execution plan it's relative cost is 96% compared to 4%.
It is not surprising that the performance is different. What is surprising is that the first query is the slow one. To wit, in this query SQL Server has full information; in the second the optimizer has no clue what is those variables and makes a blanket guess. And this time this gives better result.
Grant keeps talking about parameter sniffing, but there are not really any parameters to sniff here.
Alejandro (welcome! by the way) suggested using OPTION (RECOMPILE) and this should help to make the second slow as well, as now the variables will be handles as constants.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]