Identity Column problem

Hi,
I have Identity column in my table, Once records is inserted into table I
pull some records out from the table. Now I want to regenrate Identity column
number after removing some records so there is no gap in Sequence number in
my table ..
How Can I regenrate Identity column again ..
Thanks
-Kris
You cannot do this reliably with an IDENTITY column. You may end up changing
seeds, using identity_insert options etc. which is not worth the effort.
Generally, "Gap-less" identity columns are almost impractical. What you are
looking for, at least it seems like, is a rank based on the values in some
column. There are several solutions posted in these newsgroups, which can be
found in the google archives. Also refer to the MS knowledge base article :
186133
Anith
|||You can drop the column and add a new one which will populate itself.
But why do you want it to be consecutive?
"Kris" wrote:

> Hi,
> I have Identity column in my table, Once records is inserted into table I
> pull some records out from the table. Now I want to regenrate Identity column
> number after removing some records so there is no gap in Sequence number in
> my table ..
> How Can I regenrate Identity column again ..
> Thanks
> -Kris
|||Sounds like you are using the IDENTITY property for the wrong reasons. (See
http://www.aspfaq.com/2523)
If all you want is a seamless row counter for display purposes, you should
do that at query time as opposed to storing them. See
http://www.aspfaq.com/2427 for some ideas.
http://www.aspfaq.com/
(Reverse address to reply.)
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:777BE06D-97B0-4687-8459-75EDF26EA941@.microsoft.com...
> Hi,
> I have Identity column in my table, Once records is inserted into table I
> pull some records out from the table. Now I want to regenrate Identity
column
> number after removing some records so there is no gap in Sequence number
in
> my table ..
> How Can I regenrate Identity column again ..
> Thanks
> -Kris