title: SQL Server 2000: Why is this query w/ variables so slow vs w/o variables?
body: <p>I can't figure out why this query would be so slow with variables versus without them. I read some where that I need to enable "Dynamic Parameters" but I cannot find where to do this.</p>
  
  <pre><code>DECLARE
        @BeginDate AS DATETIME
       ,@EndDate AS DATETIME
  SELECT
        @BeginDate = '2010-05-20'
       ,@EndDate = '2010-05-25'
  
  -- Fix date range to include time values
  SET @BeginDate = CONVERT(VARCHAR(10), ISNULL(@BeginDate, '01/01/1990'), 101) + ' 00:00'
  SET @EndDate = CONVERT(VARCHAR(10), ISNULL(@EndDate, '12/31/2099'), 101) + ' 23:59'
  
  SELECT
       *
  FROM
      claim c
  WHERE
      (c.Received_Date BETWEEN @BeginDate AND @EndDate) --this is much slower
      --(c.Received_Date BETWEEN '2010-05-20' AND '2010-05-25') --this is much faster
  </code></pre>