CODEWITHSUNDEEP
sqlauto-increment
Rivka
Rivka

Reputation: 2202

Max Size of SQL Server Auto-Identity Field

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.

Upvotes: 10

Views: 24779

Answers (4)

Niraj Trivedi
Niraj Trivedi

Reputation: 2880

  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

Upvotes: 1

dave
dave

Reputation: 12806

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

Upvotes: 6

pvieira
pvieira

Reputation: 1695

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),
    (...)

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135838

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

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

Upvotes: 24

Related Questions