Determining Identity Value Capacity

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at savvy@sqlmag.com.

We have a column defined with an IDENTITY property. The column is defined as an int data type, and the table now has about 5 million rows. How many more rows can this identity column hold?

It depends. The rate at which you consume identity values depends on the data type's maximum size (your seed value) and the identity increment. You also need to remember that your identity columns might have gaps because the IDENTITY property doesn't guarantee that you'll end up with sequential numbers. Identity values are assigned to rows in the table in a sequential order based on the increment, but a transaction might be rolled back for some reason. In such a case, you would end up with a gap in the range of identity values stored in the table.

Keep the data-type size in mind if you're worried about running out of space. Identity can be based on any data type that holds a whole-number integer (e.g., bigint, numeric). Bigint is a new data type in SQL Server 2000 that stores an 8-byte integer in the range from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807), creating a total of 18,446,744,073,709,551,616 values if you include the negative values. To put this number in perspective, consider the following:

  • Approximately 31,536,000 seconds are in a year.
  • Bigint can support 18,446,744,073,709,551,616 values, including the values less than 0.
  • If you averaged 10,000 inserts per second, you would use up 315,360,000,000 identity values in a year.
  • At this rate, you would run out of values in 58,494,241 years.

The decimal data type, which is defined with the maximum precision of 38, gives you an even greater capacity for storing identity values. In other words, you're unlikely to ever run out of values. However, we've all learned a lesson from the folks who used two-digit years, thinking, "My code will never run into the next millennium." The prudent DBA should always plan for reaching the end of the identity value range.