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?

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

Use BIGINT and you likely will never reach the limit.

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