using max aggregate function on varchar column vs int column sql server

MAX aggregate Function behaves in a different way on numeric columns when compared to alpha-numeric columns.

consider the below TSQL script:
[sql]
DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT MAX(ID) FROM @vTable
[/sql]

We can expect the result of the MAX(ID) to be 199.

Now consider this TSQL Script:
[sql]
DECLARE @vTable1 TABLE (ID varchar(10))

INSERT INTO @vTable1(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT MAX(ID) FROM @vTable1
[/sql]

Now if you expect the result to be 199 again then you are wrong.
The result is 99.

Why this difference:
ID in the second script is a varchar column.
so for varchar being alpha numeric the ascii codes start with NULL for dec(0) till dec(255).
1 comes at around dec(49), 9 comes at dec(57).
So it compares 1 in 199 with 9 in 99 and sorts out that 99 is greater.
Hence the result 99.

Below query you can see the data in sorted format on the ID (varchar) column.
[sql]
DECLARE @vTable1 TABLE (ID varchar(10))

INSERT INTO @vTable1(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT ID FROM @vTable1 order by ID asc
[/sql]

Results in sql server management studio:
1
199
99

Leave a Reply

Your email address will not be published. Required fields are marked *