Identity Specification limit

what happens when a column marked as Identity Specification reaches the limit? for example, I have some code tables using tinyints as keys, the actual number of entries will be 20 or so but there is some volatility, so eventually the 255 limit will be reached, what happens then?

the same thing applies to ints or bigints used as keys, eventually the database must run out of numbers

information will be appreciated

David Wilson.

Hi,

When the maximum has been reached, an error will be generated. Here is an example:

--CREATE TABLE IDENTITYTEST
--(
-- ID TINYINT IDENTITY(1, 1),
-- TEXTVALUE VARCHAR(50)
--)

DECLARE @.COUNTER INT
SET @.COUNTER = 0

WHILE @.COUNTER < 260
BEGIN
INSERT INTO IDENTITYTEST(TEXTVALUE) VALUES ('VALUE ' + CAST(@.COUNTER AS VARCHAR(3)))
SET @.COUNTER = @.COUNTER + 1
END

/*
RESULT:
Msg 8115, Level 16, State 1, Line 12
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/

Reference: http://msdn2.microsoft.com/en-us/library/ms186775.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

right, I did about the same thing (not quite as elegant :-)) - with the same result

the question is - How do you fix it? and how do you code something into a daily checkup routine or the like to find it and fix it before it happens?

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> > what happens if IDENTITY value reaches the max value?
> You will get an overflow error.
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> > Dear All,
> >
> > what happens if IDENTITY value reaches the max value?
> >
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> >
> > Rami,
>
>

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,
> what happens if IDENTITY value reaches the max value?
You will get an overflow error.

> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,
|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:

> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.

> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:

> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSE
RT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>

Identity / Auto Incrementing Column

Hi,

I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?

trenyboy wrote:

Hi,

I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?

since int's maximum size is 2G once it's limit is exceeded you will have an arithmetic overflow. same goes with bigint.. although by then 2GB of rows this is a prime candidate for partioning already...