| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 03/27/2002 : 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
Constraint Violating Yak Guru
USA
468 Posts |
Posted - 03/27/2002 : 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
USA
992 Posts |
Posted - 03/27/2002 : 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
Constraint Violating Yak Guru
USA
468 Posts |
Posted - 03/27/2002 : 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
United Kingdom
12543 Posts |
Posted - 03/27/2002 : 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
USA
15557 Posts |
Posted - 03/27/2002 : 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
Constraint Violating Yak Guru
USA
468 Posts |
Posted - 03/27/2002 : 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>
|
|
|
| |
Topic |
|
What happens when the Identity Column has reached its highest limit numerc(18,0),will an insert fail
