title: How does sql server order by work with pipe character
body: <p>It appears that SQL server must do something special when ordering by character columns that contain pipe characters.  </p>
  
  <p>ascii('|') returns 124 as does unicode('|')</p>
  
  <p>ascii('0') returns 48 </p>
  
  <p>Order by returns '|' character before letters and numbers.</p>
  
  <p>I have tried to look at collations and in the cases I have looked at, the '|' character always has value 124.</p>
  
  <p>Here is my test select</p>
  
  <pre><code>select * from (
     select '0' as col1, ASCII('0') as col2, unicode('0') as col3
     union select '1' as col1, ASCII('1') as col2, unicode('1') as col3
     union select '|' as col1, ASCII('|') as col2, unicode('|') as col3
     union select 'a' as col1, ASCII('a') as col2, unicode('a') as col3
     union select ' ' as col1, ASCII(' ') as col2, unicode(' ') as col3
     union select '.' as col1, ASCII('.') as col2, unicode('.') as col3
     union select '/' as col1, ASCII('/') as col2, unicode('/') as col3
  
  ) as q
  order by col1
  </code></pre>
  
  <p>This query returns:</p>
  
  <pre><code>    col1  col2  col3
  1           32    32
  2      .    46    46
  3      /    47    47
  4      |   124   124
  5      0    48    48
  6      1    49    49
  7      a    97    97
  </code></pre>
  
  <p>Why does 124 fall between 47 and 48?</p>
  
  <p>The collation being used is SQL_Latin1_General_CP1_CI_AI.  </p>
  
  <p>If only binary collations use the ascii/unicode value of the characters for sorting, how can I find what the sort ordering is for characters in this collation?</p>