DELETE rows involving auto-increment fields

I tried to delete a couple of records from a table which has a ID field
which is auto-incremented(IDENTITY) . But, after deletion... when I
tried to insert new records, the ID field gets incremented from
previous next value (before deletion). Is there any way to get around
this? Plz helpLookup DBCC CHECKIDENT
example:
Force the current identity value to 30
This example forces the current identity value in the jobs table to a
value of 30.
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
http://sqlservercode.blogspot.com/|||"juventus" <saurabh.kotkar@.gmail.com> wrote in message
news:1140806282.998562.62590@.j33g2000cwa.googlegroups.com...
>I tried to delete a couple of records from a table which has a ID field
> which is auto-incremented(IDENTITY) . But, after deletion... when I
> tried to insert new records, the ID field gets incremented from
> previous next value (before deletion). Is there any way to get around
> this? Plz help
>
First of all, a lot of things can happen that may cause gaps in Identity.
You have found one. Rolled back transactions are another.
SQL only guarantees that the values will be incremental.
See DBCC Checkident in Books On-Line.|||Thanks a lot man..! It worked. I was able to reseed the increment
value..|||Confirm that you have a unique index or constraint on this column. I think
it may be possible for SQL Server to repeat sequences of identity numbers if
the maximum value for data type is reached or as a result of reseeding to a
value less than the current highest value.
"juventus" <saurabh.kotkar@.gmail.com> wrote in message
news:1140808374.186842.221090@.u72g2000cwu.googlegroups.com...
> Thanks a lot man..! It worked. I was able to reseed the increment
> value..
>