title: String concatenation in where clause
body: <p>I posted the following question yesterday: <a href="https://stackoverflow.com/questions/8721784/multiple-in-statements-in-a-where-clause-that-need-to-match-against-each-other/8721851#8721851">Multiple 'in' statements in a where clause that need to match against each other</a></p>
  
  <p>In this there was some discussion about a feature in Oracle which doesn't exist in Microsoft SQL, the specific line of code being:</p>
  
  <pre><code>where (suite_id,lease_id) in (('CCBG08','205059'),('CCBG14','152424'),('CCCF048','150659'))
  </code></pre>
  
  <p>Assuming that we have a table of (however with more rows):</p>
  
  <blockquote>
    <p>suite_id  lease_id </p>
    
    <p>CCBG08         150659</p>
    
    <p>CCBG14         152424</p>
  </blockquote>
  
  <p>I extrapolated on this to create the following solution for Microsoft SQL:</p>
  
  <pre><code>select  *
  from    property.lease_period
  where   (suite_id + ' ' + lease_id) 
          in (
              ('CCBG08 205059'),
              ('CCBG14 152424'),
              ('CCCF048 150659')
          )
  </code></pre>
  
  <p>Unfortunately the performance implications of doing something like this are quite significant (as indexes aren't being used). I was wondering how this could be improved?</p>