title: SQL Encrypted Columns in WHERE Clause
body: <p>I am looking to apply SQL column level encryption using symmetric keys.  The initial steps needed to create the Database Master Key, Certificates and Symmetric Keys seems straight forward and I have tested encrypting/decrypting data using Symmetric Keys successfully.</p>
  
  <p>However, once the data is encrypted I don't know how best to query it.  E.g.</p>
  
  <pre><code>SELECT PlainTextA, PlainTextB, PlainTextC 
  WHERE CONVERT(varchar, DECRYPTBYKEY(EncyptedColumn)) = @SearchTerm
  </code></pre>
  
  <p>would surely result in a full table scan?</p>
  
  <p>Another option I thought might work is encrypting the search criteria first e.g.</p>
  
  <pre><code>SELECT PlainTextA, PlainTextB, PlainTextC 
  WHERE EncyptedColumn = ENCRYPTBYKEY(KEY_GUID('KeyName'), @SearchTerm)
  </code></pre>
  
  <p>but this doesn't work as the encrypted value generated is always different.</p>
  
  <p>Any suggestions would be greatly appreciated.</p>