Identity

I have a numerid field with an identity set on it so that every record get's
an incremented number in this field automatically.
If I delete a record, the numbering is no longer sequential as one record
has gone.
Is there any way of forcing a re-number of the records to make them
sequential again?
Thanks
You can use DBCC CHECKIDENT to reset. But there's no feature to compress "holes" in the sequence. The question
is why does it matter if they are sequential or not? They are only used to identify something anyhow! Also, be
aware that rollbacks and things like that can mean that you consume a value which isn't inserted to the table,
leading to a "gap".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>
|||In addition to Tibor's response, if you wrote a trigger or something which
did compress the values it would very likely be quite expensive... These
PK values should have no meaning...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record
get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>