title: ORDER BY ... COLLATE in SQL Server
body: <p>Under SQL Server. A table contains some text with different cases. I want to sort them case-sensitive and thought that a <code>COLLATE</code> in the <code>ORDER BY</code> would do it. It doesn't. Why?</p>
  
  <pre><code>CREATE TABLE T1 (C1 VARCHAR(20))
  INSERT INTO T1 (C1) VALUES ('aaa1'), ('AAB2'), ('aba3')
  
  SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_CS_AS
  SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_CI_AS
  </code></pre>
  
  <p>Both queries return the same, even if the first one is "CS" for case-sensitive </p>
  
  <pre><code>aaa1
  AAB2
  aba3
  </code></pre>
  
  <p>(in the first case, I want <code>AAB2, aaa1, aba3</code>)</p>
  
  <p>My server is a SQL Server Express 2008 (10.0.5500) and its default server collation is <code>Latin1_General_CI_AS</code>.</p>
  
  <p>The collation of the database is <code>Latin1_General_CI_AS</code> too.</p>
  
  <p>The result remains the same if I use <code>SQL_Latin1_General_CP1_CS_AS</code> in place of <code>Latin1_General_CS_AS</code>.</p>