title: Why Query Cost is So different for same type of Query?
body: <p>Here is my SQL query in SQL Server 2008.</p>
  
  <p>Three identical queries, with different approaches</p>
  
  <pre><code>Declare @UploadDetailID nvarchar(500) = '62703,62709,67161,67167,74580,76728,76774,76777,89001,116048,602337,52674,626855,626863,626877,626862,626874,626861,626873,626857,626860,626872,636929,636938,             636925,636937,636924,636920,636922,636934,636923,644566,644574,644565,644577,644564,644563,644561,646566,646578,646567,646575,646565,646562,646564,653093,653096,653103';
  Begin
      select top 500 * 
      from SignatureDetail 
      where (Case When @UploadDetailID = '' then 1 
                  When ','+@UploadDetailID+',' like '%,' + ltrim(UploadDetailID) + ',%' then 1
                  Else 0 
             End) = 1
  
      select top 500 * 
      from SignatureDetail 
      where ',' + @UploadDetailID + ',' like '%,' + ltrim(UploadDetailID) + ',%'
  
      select top 500 * 
      from SignatureDetail 
      where (Case when ',' + @UploadDetailID + ',' like '%,' + ltrim(UploadDetailID) + ',%' then 1 else 0 End) = 1
  End
  </code></pre>
  
  <p>Here are the query costs:</p>
  
  <p><a href="https://i.stack.imgur.com/Nzjba.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Nzjba.png" alt="enter image description here"></a></p>
  
  <p><strong>(1)</strong> First query with <code>Case</code> and two <code>When</code> conditions is taking only 4% of query cost</p>
  
  <pre><code>select top 500* from SignatureDetail where 
      (Case when @UploadDetailID='' then 1 
          When ','+@UploadDetailID+',' like '%,'+ltrim(UploadDetailID)+',%' then 1
          else 0 End)=1
  </code></pre>
  
  <p><strong>(2)</strong> Second query without <code>Case</code> condition is taking 48% of the total query cost</p>
  
  <pre><code>select top 500* from SignatureDetail where  ','+@UploadDetailID+',' like '%,'+ltrim(UploadDetailID)+',%'
  </code></pre>
  
  <p><strong>(3)</strong> Third query with <code>Case</code> and one <code>When</code> condition is also taking 48% of the total query costs</p>
  
  <pre><code> select top 500* from SignatureDetail where  (Case when ','+@UploadDetailID+',' like '%,'+ltrim(UploadDetailID)+',%' then 1 Else 0 End)=1
  </code></pre>
  
  <p>Why do the second and third query take up so much of the total query costs?</p>