title: Using "CASE" in Where clause to choose various column harm the performance
body: <p>I have query which needs to be dynamic on some of the columns, meaning I get a parameter and according its value I decide which column to fetch in my Where clause. I've implemented this request using "CASE" expression:</p>
  
  <pre><code>(CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) 
  &gt;= DATEADD(mi, -@TZOffsetInMins, @sTime) 
   AND (CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) 
   &lt; DATEADD(mi, -@TZOffsetInMins, @fTime)
  </code></pre>
  
  <p>If <code>@isArrivalTime = 1</code> then chose <code>ArrivalTime</code> column else chose <code>PickedupTime</code> column. I have a clustered index on <code>ArrivalTime</code> and nonclustered index on <code>PickedupTime</code>.</p>
  
  <p>I've noticed that when I'm using this query (with <code>@isArrivalTime = 1</code>), my performance is a lot worse comparing to only using <code>ArrivalTime</code>. </p>
  
  <p>Maybe the query optimizer can't use\choose the index properly in this way?</p>
  
  <p>I compared the execution plans an noticed that when I'm using the <code>CASE</code> 32% of the time is being wasted on the index scan, but when I didn't use the CASE<code>(just used</code>ArrivalTime`) only 3% were wasted on this index scan.</p>
  
  <p>Anyone know the reason for this?</p>