Reseed identity column in SQL 2000

Hello. I see that we're not permitted to reseed a incremental column to a
value that's lower than the highest value in the table. I guess that's in
there for our protection but is there any way around this? On an integer
column I manually set row to a value of 2 billion and had planned to reseed
to a very low number never to approach it. If we have to start at 2 billion
it's possible that we'll run out of room.
Guess I can change the column to a large int. Is anyone thinking that
identity fields are more hassle than they're worth?
TIA,
Ken Trockktrock wrote:
> Hello. I see that we're not permitted to reseed a incremental column to a
> value that's lower than the highest value in the table. I guess that's in
> there for our protection but is there any way around this? On an integer
> column I manually set row to a value of 2 billion and had planned to resee
d
> to a very low number never to approach it. If we have to start at 2 billio
n
> it's possible that we'll run out of room.
> Guess I can change the column to a large int. Is anyone thinking that
> identity fields are more hassle than they're worth?
> TIA,
> Ken Trock
I think you are referring to this comment in Books Online:
"If the value of new_reseed_value is less than the maximum value in the
identity column, error message 2627 will be generated on subsequent
references to the table."
http://msdn.microsoft.com/library/d...asp?frame=true
This implies that setting a value lower than the maximum won't work but
if you know that error message 2627 is a key violation error you'll
realize that a problem can only occur when and if you violate a
constraint by generating a duplicate value. So in fact you can do
exactly what you have described.
I notice this is slightly clearer in 2005 BOL:
"If the value of new_reseed_value is less than the maximum value in the
identity column and a PRIMARY KEY or UNIQUE constraint exists on the
identity column, error message 2627 will be generated on subsequent
references to the table."

> Guess I can change the column to a large int. Is anyone thinking that
> identity fields are more hassle than they're worth?
Yes they are a gerat big hassle. A lot of the time IDENTITY columns are
woefully and needlessly misused and abused by people who don't even
realize what they are doing wrong.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||of course you can't do that.
why are you adding a row with this very high value? is it some kind of
stub row? why not make it 0 or go negative with it instead?
ktrock wrote:
> Hello. I see that we're not permitted to reseed a incremental column to a
> value that's lower than the highest value in the table. I guess that's in
> there for our protection but is there any way around this? On an integer
> column I manually set row to a value of 2 billion and had planned to resee
d
> to a very low number never to approach it. If we have to start at 2 billio
n
> it's possible that we'll run out of room.
> Guess I can change the column to a large int. Is anyone thinking that
> identity fields are more hassle than they're worth?
> TIA,
> Ken Trock|||if you really want to do that, move all the rows with idenities higher
than the value to another table, set the seed to the value, and move
the rows back with IDENTITY_INSERT on|||holy crap - i always thought this was a silly enough thing to be want to
do, but to actually be able to do it...
Ken - ok - i was wrong, you can do it. but i still say you shouldn't
(and shouldn't be able to...)
David Portas wrote:
> ktrock wrote:
>
>
> I think you are referring to this comment in Books Online:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
> http://msdn.microsoft.com/library/d...asp?frame=true
> This implies that setting a value lower than the maximum won't work but
> if you know that error message 2627 is a key violation error you'll
> realize that a problem can only occur when and if you violate a
> constraint by generating a duplicate value. So in fact you can do
> exactly what you have described.
> I notice this is slightly clearer in 2005 BOL:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column and a PRIMARY KEY or UNIQUE constraint exists on the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
>
>
>
> Yes they are a gerat big hassle. A lot of the time IDENTITY columns are
> woefully and needlessly misused and abused by people who don't even
> realize what they are doing wrong.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks all for the replies. Will try 1 of these options.
Ken
"David Portas" wrote:

> ktrock wrote:
> I think you are referring to this comment in Books Online:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
> http://msdn.microsoft.com/library/d...asp?frame=true
> This implies that setting a value lower than the maximum won't work but
> if you know that error message 2627 is a key violation error you'll
> realize that a problem can only occur when and if you violate a
> constraint by generating a duplicate value. So in fact you can do
> exactly what you have described.
> I notice this is slightly clearer in 2005 BOL:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column and a PRIMARY KEY or UNIQUE constraint exists on the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
>
> Yes they are a gerat big hassle. A lot of the time IDENTITY columns are
> woefully and needlessly misused and abused by people who don't even
> realize what they are doing wrong.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>sql