January 19, 2015 at 10:07 pm
Comments posted to this topic are about the item The Identity Limit
January 19, 2015 at 10:17 pm
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
January 20, 2015 at 1:10 am
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
January 20, 2015 at 1:18 am
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 ON
insert into i (o)
select 99999999999999999999999999999999999999 --38 digit
January 20, 2015 at 2:21 am
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.
January 20, 2015 at 2:38 am
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
January 20, 2015 at 2:41 am
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 20, 2015 at 2:44 am
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
January 20, 2015 at 2:56 am
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
January 20, 2015 at 3:13 am
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.
January 20, 2015 at 4:42 am
10 ^38 > 8 ^21 = 2 ^63
January 20, 2015 at 4:44 am
...........................
January 20, 2015 at 4:44 am
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.
January 20, 2015 at 4:45 am
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.
January 20, 2015 at 4:46 am
---------------------
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply