Search code examples
sql-servert-sqlsql-server-2017

Varchar and nvarchar different implicit conversion to int

SQL Server 2017. Collation: SQL_Latin1_General_CP1_CI_AS

Legacy database.

The following code:

DECLARE @qav nvarchar(255)
SET @qav = '-89'

SELECT CASE WHEN @qav < '0' THEN 1 ELSE 0 END as char_test

----=====================================

DECLARE @qav1 varchar(255)
SET @qav1 = '-89'

SELECT CASE WHEN @qav1 < '0' THEN 1 ELSE 0 END as char_test

Results in the different results:

char_test   
----------- 
0           

char_test   
----------- 
1

Can anyone explain why nvarchar '-89' < '0' is returning a different result when using varchar and nvarchar?

Solution

  • For implicit conversions int has a higher precedence than varchar and nvarchar(Implicit and explicit conversion).
    So in this case:

    '-89' < 0

    '-89' is converted to int and it is equivalent to:

    -89 < 0

    which is TRUE.