9

What is the max size of SQL Server identity field (int)?

I am deleting and inserting hundereds of records at a time a few times a day in a few tables and I'm curious what effect this will have regarding the auto-identity field.

I can run a job every night or so and truncate this data, if needed.

I'd appreciate your thoughts.

Thank you.

Rivka
  • 2,172
  • 10
  • 45
  • 74

4 Answers4

23

An INT will take you up to 2,147,483,647.

A BIGINT will get you 9,223,372,036,854,775,807.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Thanks. In one day, I hit half a million... so I probably wouldn't be able to use either without truncating every so often? – Rivka Dec 22 '11 at 14:27
  • 17
    @Becky At half a million per day, a BIGINT would *only* last for about 50,539,024,859 years. :-) – Joe Stefanelli Dec 22 '11 at 14:31
  • OK, there's my answer then. I'm not going to worry what happens after that :). – Rivka Dec 22 '11 at 14:34
  • 3
    @Becky: you'll *probably* be retired by then :-) – marc_s Dec 22 '11 at 14:40
  • Yes, but think about the people how have to support the database then. – pvieira Dec 22 '11 at 14:41
  • @pvieira: I'll admit, that thought actually did cross my mind for a second, but I don't think anyone will be here anymore at that point in time... I'm still worried about the affect BIGINT will affect our DB right now though. We have a massive DB and I need to be concerned with each table setup I create. Thoughts please? – Rivka Dec 22 '11 at 14:48
  • 2
    @Becky if you use INT you have 4293 days. Isn't that enough? Or doing a truncate in every 11.7 years is to much? :P – aF. Dec 22 '11 at 14:52
  • Thanks guys. I left it as int and created a job to truncate once a year. – Rivka Dec 22 '11 at 15:22
6

2^31 - 1 (2,147,483,647) is the upper range of an int

dave
  • 12,406
  • 10
  • 42
  • 59
2

The limit is related to the datatype itself, not with the fact of being auto increment.

If you are concerned about the maximum size, you can start the sequence below zero, and thus double the capacity, like this:

CREATE TABLE [MYTABLE](
[ID] [int] IDENTITY(-2147483648,1),
    (...)
pvieira
  • 1,687
  • 6
  • 17
  • 32
  • That's an interesting idea. But, still, should there still be a concern for the future, since this deleting/inserting will be taking place every date at least once or twice a day from now and on? – Rivka Dec 22 '11 at 14:30
  • 1
    If you can afford it, you can do a TRUNCATE TABLE instead of DELETE; that you the identity value will be reset, and not incremented. – pvieira Dec 22 '11 at 14:37
  • I can't afford that. See http://stackoverflow.com/questions/8564405/sql-server-tables-specific-to-each-user – Rivka Dec 22 '11 at 14:49
1
  1. bigint - Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) Storage: 8 Bytes
  2. int - Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) Storage: 4 Bytes
  3. smallint - Range -2^15 (-32,768) to 2^15-1 (32,767) Storage: 2 Bytes
  4. tinyint - Range 0 to 255 Storage: 1 Bytes
Niraj Trivedi
  • 2,370
  • 22
  • 24