The Identity Limit
-
Comments posted to this topic are about the item The Identity Limit
-
CREATE TABLE TEST
(
nIDENTITY numeric(38,0) identity(10000000000000000000000000000000000000,1),
nNo int)
INSERT TEST
SELECT 1
UNION ALL
SELECT 2
SELECT LEN(nIDENTITY),nNo
FROM TEST
-
Maybe the test script should have created a table with an identity column of numeric(38,0), instead of numeric(17,0).
Hence, the "correct" answer is incorrect.
(I though I was losing my sanity here for a moment :-D)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (1/20/2015)
Maybe the test script should have created a table with an identity column of numeric(38,0), instead of numeric(17,0).Hence, the "correct" answer is incorrect.
(I though I was losing my sanity here for a moment :-D)
+1
create table i(o decimal(38) identity)SET IDENTITY_INSERT i ONinsert into i (o)select 99999999999999999999999999999999999999 --38 digit -
I thought it was 10^38 - 1 which works for a DECIMAL(38,0) type. That's a much bigger number than the BIGINT type gives you.
-
Richard Warr (1/20/2015)
I thought it was 10^38 - 1 which works for a DECIMAL(38,0) type. That's a much bigger number than the BIGINT type gives you.You're correct. There's an issue with the test script provided in the explanation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Richard Warr (1/20/2015)
I thought it was 10^38 - 1 which works for a DECIMAL(38,0) type. That's a much bigger number than the BIGINT type gives you.That's what I thought too. I'm not completely au fait with scientific notation but I thought 10^38 was 10 followed by 38 zeros. Bigint is has only 18 zeros so it's considerably smaller. I'm prepared to be corrected though.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
BWFC (1/20/2015)
I'm prepared to be corrected though.No need, the correct answer is actually not so correct
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
The explanation script is definitely wrong - it defines the columns as being numeric(17,0) i.e. 17 digits long and then tries to insert a 19 digit number, which obviously fails.
Change myid to NUMERIC(38,0) and the script works without error - even going as far as this:
INSERT dbo.id_test2 (myid) VALUES (99999999999999999999999999999999999990)
But to prove that Identity is still working I modified the table to include a second column and inserted additional rows letting SQL Server populate the ID column. Only when trying to add the next row after these did an error occur:
myid b
99999999999999999999999999999999999990NULL
999999999999999999999999999999999999911
999999999999999999999999999999999999922
999999999999999999999999999999999999933
999999999999999999999999999999999999944
999999999999999999999999999999999999955
999999999999999999999999999999999999966
999999999999999999999999999999999999977
999999999999999999999999999999999999988
999999999999999999999999999999999999999
-
Yeah, I thought the answer was going to be 10^38 - 1 since that's the max value for a NUMERIC column, and those can be defined as IDENTITY.
-
10 ^38 > 8 ^21 = 2 ^63
-
...........................
-
Thank you for the post, Steve, good one.
(From the previous qtod on identity column and its datatype, i was sure that last choice was right,but somehow i ended up selecting bigint and it showed as correct answer...i was confuesd:w00t: and how that cane right?..... I got the point for selecting wrong answer, and when Steve is going to allot the points back, i am going to lose mine.)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
matthew.flower (1/20/2015)
The explanation script is definitely wrong - it defines the columns as being numeric(17,0) i.e. 17 digits long and then tries to insert a 19 digit number, which obviously fails.Change myid to NUMERIC(38,0) and the script works without error - even going as far as this:
INSERT dbo.id_test2 (myid) VALUES (99999999999999999999999999999999999990)
But to prove that Identity is still working I modified the table to include a second column and inserted additional rows letting SQL Server populate the ID column. Only when trying to add the next row after these did an error occur:
myid b
99999999999999999999999999999999999990NULL
999999999999999999999999999999999999911
999999999999999999999999999999999999922
999999999999999999999999999999999999933
999999999999999999999999999999999999944
999999999999999999999999999999999999955
999999999999999999999999999999999999966
999999999999999999999999999999999999977
999999999999999999999999999999999999988
999999999999999999999999999999999999999
+1
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
---------------------
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply