2

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
RandyMcKay
  • 326
  • 2
  • 15

2 Answers2

3

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thank you @forpas for the explanation. – RandyMcKay Dec 13 '19 at 16:02
  • why did you edit the answer though? converted ints comparison is evident. it is varchar and nvarchar comparison which was interesting. – RandyMcKay Dec 13 '19 at 16:13
  • 1
    @RandyMcKay I'm working on it because I found something interesting/starnge. – forpas Dec 13 '19 at 16:14
  • Check these: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3064c10f75707c6fe0fa1a9648676a39 and http://sqlfiddle.com/#!18/0d32a/16 – forpas Dec 13 '19 at 16:42
  • 1
    @RandyMcKay Finally this is what you (and I) are looking for: https://stackoverflow.com/a/3057194/10498828 – forpas Dec 13 '19 at 17:10
2

Strings and numbers are completely different. Strings are sorted left to right by each character, numbers are assorted in numerical order. This is just one reason why using the correct data type is so important.

'10' is less than '2' because '2' is greater than '1'; therefore '10' is less than '2'. '09-12-2019' is "before" '31-01-1924' because '3' is greater than '0'.

If you are comparing numbers, use numerical data types, not strings. If you're comparing dates (and times) use a Date and Time data type, not a string.

Appears what the OP is actually asking about it Data Type Precedence. When 2 expressions are of different data types and compared the Data Precedence is used to implicitly convert one side of the expression to be the same as the other.

In your query you have 2 expressions: @qav1 < '0' and @qav1 < 0. The former will not be effected by Data Type Precedence, as both sides are the same. As a result the - is compared to the 0, which has a larger value , and so 1 is returned.

For the second second expression, '-89' is implicitly converted to an int, due to Data Type Precedence, and so the expression beomces -89 < 0. This is true, and so 1 is returned.

SQL Server uses the following precedence order for data types:

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar (including nvarchar(max) )
  26. nchar
  27. varchar (including varchar(max) )
  28. char
  29. varbinary (including varbinary(max) )
  30. binary (lowest)
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • I was not asking about the logic of comparison, this is not my code first of all. I am just confused why implicit conversion works differently. – RandyMcKay Dec 13 '19 at 15:48
  • 2
    [Data Type Precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15), @RandyMcKay . Was unclear, so i will update the answer. – Thom A Dec 13 '19 at 15:49
  • @RandyMcKay The only _implicit_ conversion in the case of character comparisons is from `'0'` to `N'0'`, so the comparison is still "alphabetical". See [data type precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15) for the rules. – HABO Dec 13 '19 at 15:50
  • Thank you Larnu. That's clear now. Saw the bug in legacy database and wondered why was that... – RandyMcKay Dec 13 '19 at 15:59
  • @Larnu `@qav1 < '0'` _does_ require a type conversion because `DECLARE @qav nvarchar(255)` does not match the type of `'0'`. A conversion to `N'0'` will be done. – HABO Dec 13 '19 at 16:03
  • I was working on the `varchar` one, @HABO . Per the examples in the original part of my answer. Otherwise I would have said the OP had **4** expressions, not 2. – Thom A Dec 13 '19 at 16:06
  • And in strings comparison, why would N'-98' < N'0' be different to '-98' < '0'? – RandyMcKay Dec 13 '19 at 16:15
  • It wouldn't be , @RandyMcKay . – Thom A Dec 13 '19 at 16:18
  • In the first script,'-98' is nvarchar, so according to Data type precedence '0' is converted to N'0' and it gives 0 result in SSMS based on comparison N'-98' < N'0' In the second script, '-98' is varchar and based on Data type precedence '0' is converted to varchar '0' and it gives 1 in SSMS based on comparison '-98' < '0' – RandyMcKay Dec 13 '19 at 16:22
  • The code you have posted **does not** produce that behaviour, @RandyMcKay [db<>Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f0498e1430562d8028191a90237a0a97) – Thom A Dec 15 '19 at 14:07