title: Search on encrypted varchar fields in SQL Server
body: <p>I have dived deep into this issue and have come up with some solutions, but I would like your opinions before I choose one. </p>
  
  <p>Database: SQL Server 2014</p>
  
  <p>I have three varchar columns <code>FirstName, MiddleName, LastName</code>.</p>
  
  <p>I have encrypted these 3 columns, i.e. data within these columns is encrypted.</p>
  
  <p>My application has a functionality for the operator to do a name search so that when the operation enters the <code>name</code> in the order <code>LastName FirstName MiddleName</code>, the database needs to search for it. The name would not be the exact match in these three columns - so a <code>like</code> query is required.</p>
  
  <p>Because these columns are encrypted, you can not directly apply <code>like</code> operator on the concatenation of three columns. You would have to decrypt the values while concatenating and then apply the <code>like</code> operator - which will be just too slow considering the size of the database (~million rows). Also you can not index encrypted columns. </p>
  
  <p>Second option is to not encrypt <code>LastName</code> (as any person can not be identified by just the last name) - have an index on <code>LastName</code> and then perform <code>like</code> query by decrypting and concatenating other two columns. I still feel that this is not going to work.</p>
  
  <p>Third option is that, before <code>INSERTING</code> new row, concatenate the string as required by search <code>(LastName + ' ' + FirstName + ' ' + MiddleName)</code>, calculate SOUNDEX and then store the SOUNDEX in a separate column which can then be indexed and searched upon. </p>
  
  <p>Can someone please shed some light on this and also suggest if you have any other different methods to search? </p>
  
  <p>Thanks.</p>