Identity column limitation

Question

Identity column limitation

sqldba4u

Hall of Fame

Points: 3414
More actions
January 12, 2010 at 4:48 am

#215409

What is the limit of an identity column defined in sql server?If there is any limit, how can we resolve it?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by 'limit'?

Identity is not a data type, it's just a property of a numeric column. You can set identity on an int column for a max value of 2 billion, or a smallint (for a max value of 32 thousand) of a bigint (for a really high max)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqldba4u Hall of Fame Points: 3414 More actions · Answer 2

After 2 billion insertions or max of the datatype limit the identity column has reached what will happen and how to tackle that?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's not a limitation of identity. That's a limitation of the data type you used on that column. As I said, identity is not a data type. The data type that you used is INT. INT has a max value of 2 billion.

The solution's the same as any time the column's too small for the value. Alter the column to a larger data type, specifically here that'll be BIGINT.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dugi SSCoach Points: 17998 More actions · Answer 4

Vivek29 (1/12/2010)
After 2 billion insertions or max of the datatype limit the identity column has reached what will happen and how to tackle that?

Did you reach a 2 billion records in your table ... usually the tables cannot have 2 billion records, at least I don't know any case, all data in DB are organized with tables which have relations, applicable with second & third normal form!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 5

...also GilaMonster has great experience with SQL Server (she's MVP also) and I think that she wrote enough info for what you ask!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

sqldba4u Hall of Fame Points: 3414 More actions · Answer 6

Thanks for the reply.I know very well that identity is a property not a datatype.I asked this because I had in my mind what would happen if the column on which identity has been defined will reach its max due to any datatype limit.

LutzM SSC Guru Points: 107049 More actions · Answer 7

I'd expect you'll see an error message of the following type:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 300.

But, as stated several times before, this error has nothing to do with the column being identity or not.

If you feel that bigint wouldn't be enough you could go with NUMERIC(38,0) (10^38 -1). But you should be aware of the increased size (8byte vs. 19 byte). If that's not enough either, you should reevaluate your database design.... (actually, that should be your first step...). 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Dugi (1/12/2010)
usually the tables cannot have 2 billion records

Why do you say that? There's no inherent restriction that stops tables from having 2 billion rows. It's easily possible on larger databases where the table is storing some kind of time-based records (hits on an advert, phone calls made, bank transactions, etc)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dugi SSCoach Points: 17998 More actions · Answer 9

I said, because meant if you start form 1 till 2 000 000 000 will take long time (maybe 1 year on large companies like banks and telephony), I think. So on the other hand the bank transactions phone calls usually are combined numbers for example date and nr of transaction or anything else that generate the long number like:

2010011211122200001 ( 2010 01 12 - current date, 111222 - phone number and 00001 sequent nr incrementing by any function ) = 2 010 011 211 122 200 001 (...and it's more than 2 billion). also other examples like that!

I can say that it was wrong example that cannot exceeded 2 000 000 000, if we discuss for the large companies, exactly you are right! :hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Dugi (1/12/2010)
I said, because meant if you start form 1 till 2 000 000 000 will take long time (maybe 1 year on large companies like banks and telephony), I think.

Maybe, but if 5-20 years of data has to be retained online, those tables get big.

So on the other hand the bank transactions phone calls usually are combined numbers for example date and nr of transaction or anything else that generate the long number like:
2010011211122200001 ( 2010 01 12 - current date, 111222 - phone number and 00001 sequent nr incrementing by any function ) = 2 010 011 211 122 200 001 (...and it's more than 2 billion). also other examples like that!

Not if they're doing their database design properly. What you've just described is a very blatant violation of 1st Normal Form.

Maybe they are using identity, maybe not, doesn't matter. Just saying that 2 billion rows is not impossible in a single table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Dugi (1/12/2010)
Did you reach a 2 billion records in your table ... usually the tables cannot have 2 billion records, at least I don't know any case, all data in DB are organized with tables which have relations, applicable with second & third normal form!

Why not? I'm happily playing with a rowcount very similar to this today.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dugi SSCoach Points: 17998 More actions · Answer 12

Ok, let me say that I'm wrong and let consider the small databases!

😎

I said before that large and medium DBs have tables that the Identity Col has generated value with combination, and the last value can be sequence number that generated with any functions that plays like Identity incremental function.

I didn't see that one table started from 1 and increases till 2-10 or 100 billion number (maybe!).

😎

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Dugi (1/12/2010)
I said before that large and medium DBs have tables that the Identity Col has generated value with combination, and the last value can be sequence number that generated with any functions that plays like Identity incremental function.

A composite column (one constructed of more than one value) is a violation of first normal form and should not appear in a well-designed database. Setting up a column with a couple ov values and a sequence is difficult to get right and absolutely unnecessary (as well as being bad design)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 14

Dugi (1/12/2010)
Vivek29 (1/12/2010)
After 2 billion insertions or max of the datatype limit the identity column has reached what will happen and how to tackle that?
Did you reach a 2 billion records in your table ... usually the tables cannot have 2 billion records, at least I don't know any case, all data in DB are organized with tables which have relations, applicable with second & third normal form!

The amount of data in a table or you can say the size of it is only limited to your disk space. And for a auto-incremental property there is no such limit as you can have your seed for an identity that starts from a 1 or 100000, with incremental periods.

As Gail said, the value on your column is limited by its data type only. I myself worked with a table having 0.5 Billion records that too on SQL 2000, and yes it was difficult to manage it, and we did lot of archiving on it...

HTH...

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."