title: Weird SQL Server 2005 Collation difference between varchar() and nvarchar()
body: <p>Can someone please explain this:</p>
  
  <pre><code>SELECT 
    CASE WHEN CAST('iX' AS nvarchar(20)) 
        &gt; CAST('-X' AS nvarchar(20)) THEN 1 ELSE 0 END,
    CASE WHEN CAST('iX' AS varchar(20)) 
        &gt; CAST('-X' AS varchar(20)) THEN 1 ELSE 0 END
  </code></pre>
  
  <p><strong>Results:</strong> 0 1</p>
  
  <pre><code>SELECT 
    CASE WHEN CAST('i' AS nvarchar(20)) 
        &gt; CAST('-' AS nvarchar(20)) THEN 1 ELSE 0 END,
    CASE WHEN CAST('i' AS varchar(20)) 
        &gt; CAST('-' AS varchar(20)) THEN 1 ELSE 0 END
  </code></pre>
  
  <p><strong>Results:</strong> 1 1</p>
  
  <p>On the first query, the <code>nvarchar()</code> result is not what I'm expecting, and yet removing the <code>X</code> make the <code>nvarchar()</code> sort happen as expected.</p>
  
  <p>(My original queries used the <code>''</code> and <code>N''</code> literal syntax to distinguish <code>varchar()</code> and <code>nvarchar()</code> rather than <code>CAST()</code> and got the same result.)</p>
  
  <p>Collation setting for the database is <code>SQL_Latin1_General_CP1_CI_AS</code>.</p>