Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

Top Categories · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

How the max(id) value differ based on Datatype

Feb 5 2013 12:00AM by abhIShek BandI   

How the max value differ based on Datatype.

in the below example if the data type is NVARCHAR , max(id) = -1

if the data type is VARCHAR then max(id) = 1

Can any one tell me how we are getting different results?


CREATE TABLE #TBL(ID NVARCHAR(10))
GO
INSERT INTO #TBL 
SELECT '1' UNION ALL
SELECT '-1'
GO
SELECT MAX(ID) FROM #TBL
GO
DROP TABLE #TBL


CREATE TABLE #TBL(ID VARCHAR(10))
GO
INSERT INTO #TBL 
SELECT '1' UNION ALL
SELECT '-1'
GO
SELECT MAX(ID) FROM #TBL
GO
DROP TABLE #TBL 

Submitted under: Microsoft SQL Server · TSQL ·  ·  · 


abhIShek BandI
106 · 2% · 503

8 Replies

  • Which version of SQL Server are you using? I think the MAX on VARCHAR does the implicit integer convertion. But I do not see this behaviour in 2012 Express edition

    commented on Feb 5 2013 6:29AM
    Madhivanan
    2 · 40% · 13039
  • Unicode sorting ignores minus char in string - http://www.unicode.org/reports/tr10/

    CREATE TABLE #TBL(ID NVARCHAR(20))
    GO
    INSERT INTO #TBL 
    SELECT 'aaa----bbb1' UNION ALL
    SELECT '-----aaa-bbb2'
    GO
    SELECT MAX(ID) FROM #TBL
    GO
    
    DROP TABLE #TBL
    
    CREATE TABLE #TBL(ID NVARCHAR(20))
    GO
    INSERT INTO #TBL 
    SELECT 'aaa----bbb2' UNION ALL
    SELECT '-----aaa-bbb1'
    GO
    SELECT MAX(ID) FROM #TBL
    GO
    
    commented on Feb 5 2013 10:11AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • Hi Madhivanan, We are using below version.

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

    commented on Feb 6 2013 1:11AM
    abhIShek BandI
    106 · 2% · 503
  • Hi Leszek Gniadkowski ,

    I didnt get your answer, Let me explain my question clearly.

    When i use data type NVARCHAR and select max(id) , am getting output as '-1' and when i use VARCHAR datatype and select max(id) am getting output as '1'.

    Why am getting the different answers based on Data type?

    commented on Feb 6 2013 1:13AM
    abhIShek BandI
    106 · 2% · 503
  • thanks both of you.. for ur present...

    commented on Feb 6 2013 1:14AM
    abhIShek BandI
    106 · 2% · 503
  • Some hints:

    1. From http://www.unicode.org/reports/tr10/ Treatment of punctuation varies. In some situations a punctuation character is treated like a base letter. In other situations, it should be ignored if there are any base, accent, or case differences. There may also be a final, tie-breaking level (called an identical level), whereby if there are no other differences at all in the string, the (normalized) code point order is used.

      Table 2. Comparison Levels Level Description Examples L1 Base characters role < roles < rule L2 Accents role < rôle < roles L3 Case/Variants role < Role < rôle L4 Punctuation role < “role” < Role Ln Identical role < ro□le < “role”

    2. nvarchar is unicode type, '1' is "identical" as '-1' or '--------1------', beacuse of '-' ignoring, but if there are no other differences at all in the string, the (normalized) code point order is used., and '1' < '-1', but '1' > '-1a'

    3. Try SELECT MAX(ID COLLATE Latin1GeneralBin) FROM #TBL

    4. I think, that SQL Server 2012 also uses unicode collation for sorting varchar type, instead of earlier versions (same result of nvarchar and varchar - '-1')

    commented on Feb 6 2013 2:12AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • Hi Abishek,

    Nice abservation,

    After had a look on above post and comments, If DataType Is Nvarchar then it simply ignores the MINUS.

    Thanks for sharing

    commented on Feb 6 2013 9:15PM
    Bala Krishna
    84 · 2% · 676
  • Hi Leszek Gniadkowski ,

    Thank you very much for your valuable information.

    commented on Feb 7 2013 7:53AM
    abhIShek BandI
    106 · 2% · 503

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]