title: SQL*Server Constant values for user defined scalar functions - performance
body: <p>I have a query that performs quite nicely when run like this:</p>
  
  <pre><code>SELECT YADAYADA FROM MYTABLE WHERE FVAL &lt;= 100 AND TVAL &gt;= 100
  </code></pre>
  
  <p>Since there's an index for (FVAL,TVAL), the query is totally optimal as a nonclustered index seek for the whole query.</p>
  
  <p>Now, it'd be nice to use a constant returned from a user defined function here. The function will return a constant value for the <em>entire transaction</em>, not just this query. But doing this:</p>
  
  <pre><code>SELECT YADAYADA FROM MYTABLE WHERE FVAL &lt;= dbo.myVal() AND TVAL &gt;= dbo.myVal()
  </code></pre>
  
  <p>Yields suboptimal results - the query plan no longer nicely runs as an index seek, but instead insists on seeking and then <em>filtering</em>, which is obviously <strong>MUCH</strong> slower - even though in this instance my function is defined as returning a constant value in this really simple case.</p>
  
  <p>I've tried using a BETWEEN clause - no better. I've tried table-valued functions, no better (in fact, the query plan gets rapidly more complex).</p>
  
  <p>Is there <em>any</em> way of persuading SQL*Server that 'hey buddy, this is a <strong>constant</strong> value we're getting here, and to optimize the plan accordingly??</p>