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