Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datatype for such a long integer Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 5:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895

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
Posted Tuesday, February 19, 2013 5:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 2,380, Visits: 7,589
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" )!
Post #1421591
Posted Tuesday, February 19, 2013 6:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
but will the sorting work correctly on varchar datatype?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1421603
Posted Tuesday, February 19, 2013 6:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 2,380, Visits: 7,589
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



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" )!
Post #1421605
Posted Tuesday, February 19, 2013 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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
Posted Tuesday, February 19, 2013 6:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 2,380, Visits: 7,589
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.



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" )!
Post #1421607
Posted Wednesday, February 20, 2013 3:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
Thanks a lot both of you. it worked. I was thinking that something like this will also work but it didn't
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
Posted Wednesday, February 20, 2013 7:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
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
Posted Wednesday, February 20, 2013 8:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
S_Kumar_S (2/19/2013)

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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1422103
Posted Wednesday, February 20, 2013 9:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
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)

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


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.


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1422145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse