S_Kumar_S (2/19/2013)
but will the sorting work correctly on varchar datatype?
Yes. . . did you execute the code I produced? So long as you sort on the computed column "useful_faux_number" it will be correctly ordered.
IF object_id('s_sumar_s') IS NOT NULL
BEGIN
DROP TABLE s_sumar_s;
END;
CREATE TABLE s_sumar_s (
ID INT IDENTITY(1,1) NOT NULL,
faux_number VARCHAR(116) CONSTRAINT CK_faux_number CHECK (RIGHT('00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'+faux_number,116) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
useful_faux_number AS RIGHT('00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'+faux_number,116)
);
INSERT INTO s_sumar_s(faux_number)
SELECT '1267'
UNION ALL SELECT '231'
UNION ALL SELECT '428973681279460182436';
SELECT * FROM s_sumar_s ORDER BY useful_faux_number ASC;
The above produces: -
ID faux_number useful_faux_number
----------- -------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------
2 231 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000231
1 1267 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001267
3 428973681279460182436 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000428973681279460182436
Whereas, if I instead sort on the non-computer column: -
SELECT * FROM s_sumar_s ORDER BY faux_number ASC;
We get it sorted as characters, which is wrong: -
ID faux_number useful_faux_number
----------- -------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------
1 1267 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001267
2 231 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000231
3 428973681279460182436 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000428973681279460182436