CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
001
001

Reputation: 65155

What happens to the primary key Id when it goes over the limit?

If you add a new record, then delete the record, then add it again over and over, in time, sooner or later, when you add a new record the integer primary key id, it will eventually exceed 2 billion.

  1. Now what happens? SQL Server will start the primary key id from 1 again? or -1?

  2. What happens if it cycles 4 billion times; how does SQL Server know not to replace the previous data?

Upvotes: 19

Views: 7534

Answers (3)

Charlie
Charlie

Reputation: 3374

This is a comment I found on a similar question and I will leave it here for future users who might be worried about overflow of a BIGINT.

If you use a BIGINT IDENTITY starting at 1, and you insert one thousand rows every second, you need a mind-boggling 292 million years before you hit the 922 quadrillion limit

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453426

You get an error if the identity would exceed the bounds of the datatype making the rest of your question moot. You can see this by

CREATE TABLE #T
(
id INT IDENTITY(2147483647,1)
)

INSERT INTO #T
DEFAULT VALUES

INSERT INTO #T
DEFAULT VALUES /*Arithmetic overflow error converting IDENTITY to data type int.*/

GO

SELECT * FROM #T

DROP TABLE #T

Upvotes: 28

Kevin
Kevin

Reputation: 512

Use BIGINT and you likely will never reach the limit.

Upvotes: 8

Related Questions