What happens when the Identity Column has reached its highest limit numerc(18,0),will an insert fail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

What happens when the Identity Column has reached its highest limit numerc(18,0),will an insert fail

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-27 : 11:19:41

Shyam Sharma writes "I read with interest your article on Identity Column,
Its just out of curiosity that i ask this:
What happens when the Identity Column has reached its highest limit numerc(18,0),will an insert fail?(SQL server 6.5)
If yes what to do next?

In case u have the time & feel like responding.
I did check for this in the Books on line & various other articles.
May be its an oversight ..if so pl forgive & do not respond."

Jay99

468 Posts

Posted - 2002-03-27 : 11:46:32

I'll let you know as soon as this...


set nocount on
create table #temp
(
    blowup numeric(19,0) identity(1,1),
    somecol bit
)

while 1=1
insert #temp(somecol) values(1)

...finishes.

Jay
<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:51:20

Tell us the hardware you're running that on and let's get a pool going to guess the completion time!

setBasedIsTheTruepath
<O>

Jay99

468 Posts

Posted - 2002-03-27 : 12:04:26

I can tell you that if I had actually run the above script, I would run out of disk long before it would error out.

Jay
<O>

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-27 : 12:11:07

Yes - it will fail. It is quite common when an int is used.

You would usually be archiving data so that you can reset the identity seed to the begining and re-use values.

If not then you will have to redesign the structure - maybe include another field in the key.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-27 : 12:17:53

You can test this pretty quickly:

CREATE TABLE IDtest (ID tinyint NOT NULL IDENTITY(1,1))
DECLARE @ctr int
SELECT @ctr=1
WHILE @ctr<257
BEGIN
INSERT INTO IDTest DEFAULT VALUES
SELECT @ctr=@ctr+1
END

Jay99

468 Posts

Posted - 2002-03-27 : 12:27:13

wait wait wait . . .

I thought the original poster was saying there is a numeric(18,0) limit to the IDENTITY property. Sure, once the limit of the datatype is reached, further inserts will error, but what happens when the limitation of the property is reached.

Well, I just tried this . . .


create table #temp
(
    blowup numeric(18,0) identity(999999999999999999,1),
    somecol bit
)

... and then did an insert to take me out to a numeric(19,0). Sure it errored . . .

quote:

Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type numeric.
Arithmetic overflow occurred.

however with this . . .


create table #temp
(
    blowup numeric(19,0) identity(999999999999999999,1),
    somecol bit
)

I can insert to my hearts content, thus disproving what I through to be the original statement.

quote:

..Identity Column has reached its highest limit numerc(18,0)..

...ok now after typing this all out I read the 'SQL server 6.5' clause there ... <shrug> I dunno </shrug>

Jay
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources