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
Thanks for the visual. It is clear to me that none of the tables we have will ever have an identity value that large. So nothing to worry about.