title: Why is Latin1_General_CS_AS not case sensitive?
body: <p>For <code>LIKE</code> queries, the <em>Latin1_General_CS_AS</em> collation is not case-sensitive. According to a <a href="http://connect.microsoft.com/SQLServer/feedback/details/725949/case-sensitivity-doesnt-work-when-using-a-range-in-like-with-collate-latin1-general-cs-as" rel="noreferrer">bug report to Microsoft</a>, this was listed as "By Design".</p>
  
  <p>However, the <em>Latin1_General_Bin</em> collation is also case-sensitive and works exactly as expected for <code>LIKE</code> queries.</p>
  
  <p>You can see the difference in this simple query:</p>
  
  <pre><code>SELECT
      MyColumn AS Latin1_General_Bin
  FROM MyTable
  WHERE MyColumn LIKE '%[a-z]%' COLLATE Latin1_General_Bin;
  
  SELECT
      MyColumn AS Latin1_General_CS_AS
  FROM MyTable
  WHERE MyColumn LIKE '%[a-z]%' COLLATE Latin1_General_CS_AS;
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!3/c3441/1" rel="noreferrer">SQL Fiddle Demo</a>.</p>
  
  <hr>
  
  <p>My questions are:</p>
  
  <ol>
  <li>Why would this be considered "By Design" to be case-insensitive in <code>LIKE</code>?</li>
  <li>If this really is better, why is it a different behavior between the two case sensitive collations <em>_Bin</em> and <em>_CS_AS</em>?</li>
  </ol>
  
  <p>I was going to standardize on <em>Latin1_General_CS_AS</em> for any case-sensitive databases going forward, but this seems like a subtle query bug waiting to happen.</p>