CODEWITHSUNDEEP
sql-server-2005
Hadad
Hadad

Reputation: 344

Identity Columns

I've an identity column which has multiple deletes now it is not working because it reaches the max limit of the INT data type, how can I insert records in the place of deleted ones without truncating all the data?

Upvotes: 2

Views: 319

Answers (3)

gbn
gbn

Reputation: 432667

Excuse the obvious...

Did you start at 1, increment of 1? If so, change the identity to -1, -1. It requires a table rebuild but it's easier than changing to bigint. And gives you another 2 billion IDs.

Then plan your bigint migration...

Upvotes: 4

marc_s
marc_s

Reputation: 755411

You cannot "recycle" unused IDENTITY values - if you've reached the end of the INT data range, you need to change your ID column to BIGINT.

ALTER TABLE dbo.YourTable
  ALTER COLUMN YourIDColumn BIGINT

The IDENTITY property will be preserved - now you have a lot of additional ID values available for the next couple months/years to come!

Upvotes: 5

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32740

That seems like a lot of work; why not just change the primary key on the table (and any related tables) from an int to a bigint?

An int will give you a max value of 2,147,483,647.

A bigint will give you a max value of 9,223,372,036,854,775,807.

Upvotes: 3

Related Questions