SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/27/2002 :  11:19:41  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/27/2002 :  11:46:32  Show Profile  Reply with Quote
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>
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 03/27/2002 :  11:51:20  Show Profile  Reply with Quote
Tell us the hardware you're running that on and let's get a pool going to guess the completion time!

setBasedIsTheTruepath
<O>
Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/27/2002 :  12:04:26  Show Profile  Reply with Quote
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>
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/27/2002 :  12:11:07  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 03/27/2002 :  12:17:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
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


Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/27/2002 :  12:27:13  Show Profile  Reply with Quote
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>
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000