{"id":86,"date":"2011-12-21T12:57:30","date_gmt":"2011-12-21T12:57:30","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/12\/21\/using-max-aggregate-function-on-varchar-column-vs-int-column-sql-server\/"},"modified":"2011-12-21T12:57:30","modified_gmt":"2011-12-21T12:57:30","slug":"using-max-aggregate-function-on-varchar-column-vs-int-column-sql-server","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/12\/21\/using-max-aggregate-function-on-varchar-column-vs-int-column-sql-server\/","title":{"rendered":"using max aggregate function on varchar column vs int column sql server"},"content":{"rendered":"<p><strong>MAX aggregate Function behaves in a different way on numeric columns when compared to alpha-numeric columns.<\/strong><\/p>\n<p>consider the below TSQL script:<br \/>\n[sql]<br \/>\nDECLARE @vTable TABLE (ID INT)<\/p>\n<p>INSERT INTO @vTable(ID)<br \/>\nSELECT 1<br \/>\nUNION<br \/>\nSELECT 199<br \/>\nUNION<br \/>\nSELECT 99<\/p>\n<p>SELECT MAX(ID) FROM @vTable<br \/>\n[\/sql]<\/p>\n<p><em>We can expect the result of the MAX(ID) to be 199.<\/em><\/p>\n<p>Now consider this TSQL Script:<br \/>\n[sql]<br \/>\nDECLARE @vTable1 TABLE (ID varchar(10))<\/p>\n<p>INSERT INTO @vTable1(ID)<br \/>\nSELECT 1<br \/>\nUNION<br \/>\nSELECT 199<br \/>\nUNION<br \/>\nSELECT 99<\/p>\n<p>SELECT MAX(ID) FROM @vTable1<br \/>\n[\/sql]<\/p>\n<p><em>Now if you expect the result to be 199 again then you are wrong.<br \/>\nThe result is 99.<\/em><\/p>\n<p><strong>Why this difference:<\/strong><br \/>\nID in the second script is a varchar column.<br \/>\nso for varchar being alpha numeric the ascii codes start with NULL for dec(0) till dec(255).<br \/>\n1 comes at around dec(49), 9 comes at dec(57).<br \/>\nSo it compares 1 in 199 with 9 in 99 and sorts out that 99 is greater.<br \/>\nHence the result 99.<\/p>\n<p>Below query you can see the data in sorted format on the ID (varchar) column.<br \/>\n[sql]<br \/>\nDECLARE @vTable1 TABLE (ID varchar(10))<\/p>\n<p>INSERT INTO @vTable1(ID)<br \/>\nSELECT 1<br \/>\nUNION<br \/>\nSELECT 199<br \/>\nUNION<br \/>\nSELECT 99<\/p>\n<p>SELECT ID FROM @vTable1 order by ID asc<br \/>\n[\/sql]<\/p>\n<p>Results in sql server management studio:<br \/>\n1<br \/>\n199<br \/>\n99<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,3],"tags":[450,451,452,453,454],"class_list":["post-86","post","type-post","status-publish","format-standard","hentry","category-sql-server-basics","category-sqlserverpedia-syndication","tag-max-aggregate-function-varchar-vs-int","tag-max-function-on-varchar","tag-using-max-aggregate-function-on-varchar-sql-server-2005","tag-using-max-aggregate-function-on-varchar-sql-server-2008","tag-why-max-aggregate-function-displays-wrong-data-when-it-contains-numbers"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/86","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}