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.

回答1:

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

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

回答2:

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

回答3:

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

回答4:

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