SQL Server 2008: what happened if identity oversteps a maximal value of int?

Question

Imagine we have a table:

create table MYTABLE (
 id int IDENTITY(1,1)
,name varchar(10)
)

We have to insert a lot of rows into the table.

Does anybody know what will happen when a generated identity value oversteps a maximal integer value (2^63-1)?

Answer 1

An example

create table dbo.MYTABLE (
 id tinyint IDENTITY(254,1)
,name varchar(10)
)
GO
INSERT dbo.MYTABLE (name) VALUES ('row 254')
GO
INSERT dbo.MYTABLE (name) VALUES ('row 255')
GO
INSERT dbo.MYTABLE (name) VALUES ('broke')
GO

gives

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.

Answer 2

An error will occur and the insert will be lost.

Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

Answer 3

You could easily test this with a very small identity column, like decimal(1,0):

create table IdentityOverflow (id decimal(1,0) identity)
while 1=1
    insert IdentityOverflow default values

Like Oded says, this prints:

Arithmetic overflow error converting IDENTITY to data type decimal.

This holds for even the largest of integers:

create table IdentityOverflow (
    id decimal(38,0) identity(1,10000000000000000000000000000000000000))
while 1=1
    insert IdentityOverflow default values