RSS Feeds

FAQ Topics

All FAQ's

General DBA

General Developer

DBA Performance Tuning

Developer Performance Tuning

Clustering

Error Messages

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

What happens when my integer IDENTITY runs out of scope?

Before we actually look at the answer, let's recall some basics of the IDENTITY property and SQL Server's numerical data types.

You can define the IDENTITY property on columns of the INT data type and on DECIMAL with scale 0. This gives you a range of:

TINYINT	0 - 255
SMALLINT	-32.768 - 32.767
INT	-2.147.483.648 - 2.147.483.647
BIGINT	-2^63 - 2^63-1

When you decide to use the DECIMAL datatype you have a potential range from -10^38 to 10^38-1.

So, keeping this in mind, we're now ready to answer the original question here. What happens when an INTEGER IDENTITY value is about to run out of scope?

CREATE TABLE id_overflow ( col1 INT IDENTITY(2147483647,1) )

INSERT INTO id_overflow DEFAULT VALUES INSERT INTO id_overflow DEFAULT VALUES SELECT * FROM id_overflow

DROP TABLE id_overflow

(1 row(s) affected) Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

This script creates a simple table with just one column of type INT. We have also created the IDENTITY property for this column. But instead of now adding more than 2 billion rows to the table, we rather set the seed value to the positive maximum value for an INTEGER. The first row inserted is assigned that value. Nothing unusual happens. The second insert, however, fails with the above error. Apparently SQL Server does not start all over again or tries to fill the maybe existing gaps in the sequence. Actually, SQL Server does nothing automatically here. You have to do this by yourself. But what can you do in such a case?

Probably the easiest solution is to alter the data type of the column to BIGINT, or maybe right on to DECIMAL(38,0) like so:

CREATE TABLE id_overflow ( col1 INT IDENTITY(2147483647,1) )

INSERT INTO id_overflow DEFAULT VALUES ALTER TABLE id_overflow ALTER COLUMN col1 BIGINT INSERT INTO id_overflow DEFAULT VALUES

SELECT * FROM id_overflow

DROP TABLE id_overflow

col1

--------------------

2147483647 2147483648

(2 row(s) affected)

If you know in advance that your table needs to keep that many rows, you can do:

CREATE TABLE bigint_t ( col1 BIGINT IDENTITY(-9223372036854775808, 1) )

INSERT INTO bigint_t DEFAULT VALUES

SELECT * FROM bigint_t

DROP TABLE bigint_t

col1

--------------------

-9223372036854775808

(1 row(s) affected)

Or the DECIMAL(38,0) variation:

CREATE TABLE decimal_t ( col1 DECIMAL(38,0) IDENTITY(-99999999999999999999999999999999999999, 1) )

INSERT INTO decimal_t DEFAULT VALUES

SELECT * FROM decimal_t

DROP TABLE decimal_t

col1

----------------------------------------

-99999999999999999999999999999999999999

(1 row(s) affected)

One might be distressed in one's aesthetical taste by those negative numbers, but it's a fact, that one now shouldn't have to worry about running out of scope for quite some time.