datatype for such a long integer

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » datatype for such a long integer

datatype for such a long integer

Rate Topic

Display Mode

Topic Options

Author

Message

S_Kumar_S

Posted Tuesday, February 19, 2013 5:30 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:55 AM
Points: 308, Visits: 680

Hi
I got a dataset from somewhere which I have to store in sql server. It has one column which consists on numeric values with length 116.
i.e. its an integer with 116 numbers. Not even bigint support this kind on length. I can't put it in varchar as i'll need to do sort operations on it.
Are there any alternatives for this in sql server?
thanks

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1421589

Cadavre

Posted Tuesday, February 19, 2013 5:41 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

Could you do something like this?

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)
);

Then use "useful_faux_number" to sort, e.g.

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;

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1421591

S_Kumar_S

Posted Tuesday, February 19, 2013 6:10 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:55 AM
Points: 308, Visits: 680

but will the sorting work correctly on varchar datatype?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1421603

Cadavre

Posted Tuesday, February 19, 2013 6:17 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

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

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1421605

Lowell

Posted Tuesday, February 19, 2013 6:18 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 11,648, Visits: 27,768

awesome solution Cadavre!
only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do you think of this?

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(REPLICATE('0',120) + faux_number,116) NOT  LIKE '%[^0-9]%'),
useful_faux_number AS RIGHT(REPLICATE('0',120) + faux_number,116)
);

INSERT INTO s_sumar_s(faux_number)
SELECT '1267'
UNION ALL SELECT '231'
UNION ALL SELECT '428973681279460182436';


--this should fail against the constraint.
insert into s_sumar_s(faux_number) values ('Alphabits42')

SELECT * FROM s_sumar_s ORDER BY useful_faux_number ASC;

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1421606

Cadavre

Posted Tuesday, February 19, 2013 6:22 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

Lowell (2/19/2013)

awesome solution Cadavre!
only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do you think of this?

Replicate is a much better idea, didn't cross my mind. I prefer to use "LIKE" rather than "NOT LIKE" though, despite the extra code.

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1421607

S_Kumar_S

Posted Wednesday, February 20, 2013 3:05 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:55 AM
Points: 308, Visits: 680

Thanks a lot both of you. it worked. I was thinking that something like this will also work but it didn't Sad

create table testing(ID int identity,
big_number VARCHAR(116) CONSTRAINT CK_big_number CHECK (RIGHT(REPLICATE('0',120) + big_number,116) NOT LIKE '%[^0-9]%'),
useful_big_number AS RIGHT(REPLICATE('0',120) + big_number,116)
)

insert into testing
select '16456456456456456456456456456456546546456456456456456456'
union select '2'
union select '11'
union select '1'

select * from testing order by '000000000'+big_number

Cadavre (2/19/2013)

Lowell (2/19/2013)

awesome solution Cadavre!
only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do you think of this?

Replicate is a much better idea, didn't cross my mind. I prefer to use "LIKE" rather than "NOT LIKE" though, despite the extra code.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1422001

sturner

Posted Wednesday, February 20, 2013 7:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 23, 2013 12:33 PM
Points: 1,315, Visits: 2,887

insert into testing
select '16456456456456456456456456456456546546456456456456456456'
union select '2'
union select '11'
union select '1'

select * from testing
order by len(big_number), big_number

The probability of survival is inversely proportional to the angle of arrival.

Post #1422092

Jeff Moden

Posted Wednesday, February 20, 2013 8:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 32,931, Visits: 26,820

S_Kumar_S (2/19/2013)

What the dataset looks like may be very important here. Since, as you identified, that the 116 digit "integer" cannot be stored in any datatype in SQL Server other than a VARCHAR, I have to ask, what does the dataset actually look like? If it's coming across as a fixed width, right justified, character based column, then just import the entire "field" including the leading spaces (or zeroes) available in the dataset and your resulting column will, in fact, be sortable.

Otherwise, you'll need to do as what Cadavre suggested.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1422103

S_Kumar_S

Posted Wednesday, February 20, 2013 9:17 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:55 AM
Points: 308, Visits: 680

No, the dataset is not of fixed length and format. there may be a length of 1 and then there may be 116. So I go by proposed solution of Cadavre.

Jeff Moden (2/20/2013)

S_Kumar_S (2/19/2013)

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1422145

« Prev Topic | Next Topic »

Permissions