sql server - Execution Plan behaviour with DateTimeOffset -

i'm trying determine best way query date ranges in sql server when dealing datetimeoffset fields.

my query

i have thought (if anything) first query take more effort there need implicit conversion string datetimeoffset.

but actual query plan shows different story

and seems problem lies in estimated number of rows?

so - things being equal, why using datetimeoffset parameter rather string incorrectly estimate number of rows returned, , therefore take order of magnitude more resources execute? show query cost taking 96% of batch? query cost estimate though actual query plan?

statistics accurate , upto date , i've cleaned proc cache before running query

with literal, histogram stats used because value known @ compile time. value unknown @ compile time local variable average density statistics used instead, resulting in different row count estimates.

you either add option recompile query hint "sniff" run time variable value or use parameterized query. these methods use histogram row count estimates. latter cache plan subsequent executions use same plan regardless of value long plan remains in cache.

this general behavior , not specific datetimeoffset data type.