On my desktop (2k8) both queries perform equally:

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 141 ms, elapsed time = 148 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 156 ms, elapsed time = 148 ms.

Times may vary from run to run by couple of ms, but they are always in line with each other.

That's probably because the "generic" execution plan chosen for @yes version is identical to the "sniffing" plan.

Both are cached and reused.

So SQL Server runs effectively the same code against the same set of data - no difference to be expected.

But it's this particular case of the table structure and data distribution.

If I change the cardinality by using this"

, Field_Value = CASE WHEN ABS(CHECKSUM(NEWID()))%20 = 1 THEN 'Yes' ELSE 'No' END

then literal constant outperforms varuiable.

it's still < 5ms difference on my machine, but with total execution time around 20 ms it's noticable.

In reverse case (90% of 'YES' and 10% of 'NO') times are basically the same:

SQL Server Execution Times:

CPU time = 281 ms, elapsed time = 289 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 297 ms, elapsed time = 287 ms.

....

SQL Server Execution Times:

CPU time = 281 ms, elapsed time = 297 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 312 ms, elapsed time = 302 ms.