title: SQL Query variable vs hardcoded value
body: <p>I need explanation of something, as I couldn't find in on my own (probably because I don't know how to search for it).</p>
  
  <p>I have a SQL Server query with some Common Table Expressions in it, one of those CTEs is selecting data based on date and user being not null e.g.</p>
  
  <pre><code>WHERE 
      "dummy"."UsageEnd" &gt;= '20161001' 
      AND "dummy"."UsageEnd" &lt; '20161101' 
      AND "Users"."Login" IS NOT NULL
  </code></pre>
  
  <p>In that form this query executes in ~2 seconds, but I need to change dates to a parameter, as this query will be executed very commonly. But if i change it to:</p>
  
  <pre><code>WHERE  
      "dummy"."UsageEnd" &gt;= @start 
      AND "dummy"."UsageEnd" &lt; @end 
      AND "Users"."Login" IS NOT NULL
  </code></pre>
  
  <p>Where <code>@start</code> and <code>@end</code> are declared as either <code>datetime</code> or <code>varchar</code>:</p>
  
  <pre><code>declare @datestart datetime
  set @datestart = '20161001';
  
  declare @dateend datetime
  set @dateend = '20161101';
  </code></pre>
  
  <p>This subquery executes in 23-24 seconds, and whole query (as reminder this subquery is in CTE) is taking 7-8 minutes, when previously it was taking 12-15 seconds. </p>
  
  <p>Can someone explain it to me why comparing dates to variables increased execution time so dramatically? Also is it possible, that whole query is taking so long because when in CTE is a variable it will re-evaluate it every time instead of just one?</p>