Home > Database, SQL Server > What happens when my integer IDENTITY runs out of scope?

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) )

GO

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) )

GO

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) )

GO

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) )

GO

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.

Advertisements
Categories: Database, SQL Server
  1. saurabh
    January 26, 2009 at 5:58 pm

    plz ant one tell me after ALTER the table ..how we can send data to that new created field….

  2. Doug
    May 30, 2012 at 1:06 am

    ALTER TABLE Tablename
    ADD Columnname varhcar(50) NOT NULL DEFAULT(‘default’)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: