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?