> 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 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 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/default.asp?url=/library/en-us/tsqlref/ts_dbcc_5lv8.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
> --
>

"David Portas" wrote:

> 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 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 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/default.asp?url=/library/en-us/tsqlref/ts_dbcc_5lv8.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
> --
>
>

> 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 Trock