17

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
001
  • 62,807
  • 94
  • 230
  • 350
  • 2
    You can always change your `ID` data type to `BIGINT` and you get +/- 9 quintillion (that's **9 billion billions** !!!!!) of values..... should be enough for **quite some time to come**! – marc_s Mar 14 '11 at 22:17
  • 2
    Or even better use a [unique identifier / GUID](http://msdn.microsoft.com/en-us/library/ms187942.aspx) and you'll only run out of values when the universe implodes! :) – CraigTP Oct 06 '11 at 12:53
  • Note that the behaviour varies between different DBMS. This question is tagged with MS SQL Server, so there isn't any real confusion, but if the question included "And what about other DBMS?" then you would end up with different answers for the different platforms. – Jonathan Leffler May 26 '12 at 23:28
  • If you insert one record every second continuously it will take slightly more than 63 years before you encounter the issue. – user3658298 May 11 '18 at 08:09

3 Answers3

26

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Martin what should a SQL Server developer should do if this happen? – Imran Qadir Baksh - Baloch May 15 '14 at 06:35
  • 2
    @user960567 In most cases change the datatype to allow larger numbers. In some cases you could consider renumbering the existing rows and reset the identity seed but this should generally be avoided (keys should be considered immutable and not altered or recycled generally speaking) – Martin Smith May 16 '14 at 18:04
8

Use BIGINT and you likely will never reach the limit.

Kevin
  • 512
  • 4
  • 15
4

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

Charlie
  • 3,113
  • 3
  • 38
  • 60