I have an identity field on a table in SQL Server. The identity seed
is 1 and the identity increment is 1. If I remove a record from this
table, the identity sequence is broken. For example:

Table contents prior to record delete:
Fname(varchar), Lname (varchar), row_id (identity)
--------------------------------------------------
Smith, Jane, 1
Smith, Tom, 2
Jones, Mark 3

Delete from mytable where row_id = 2

Fname(varchar), Lname (varchar), row_id (identity)
---------------------------------------------------
Smith, Jane, 1
Jones, Mark 3

How can I re-set the identity field so that there are no gaps in the
sequence?

For example, I want to update the sequence after the delete so that
records look like this:
Fname(varchar), Lname (varchar), row_id (identity)
---------------------------------------------------
Smith, Jane, 1
Jones, Mark 2

DBA (kaylisse@yahoo.com) writes:
> I have an identity field on a table in SQL Server. The identity seed
> is 1 and the identity increment is 1. If I remove a record from this
> table, the identity sequence is broken. For example:
>
> Table contents prior to record delete:
> Fname(varchar), Lname (varchar), row_id (identity)
> --------------------------------------------------
> Smith, Jane, 1
> Smith, Tom, 2
> Jones, Mark 3
>
> Delete from mytable where row_id = 2
>
> Fname(varchar), Lname (varchar), row_id (identity)
> ---------------------------------------------------
> Smith, Jane, 1
> Jones, Mark 3
>
> How can I re-set the identity field so that there are no gaps in the
> sequence?
>
> For example, I want to update the sequence after the delete so that
> records look like this:
> Fname(varchar), Lname (varchar), row_id (identity)
> ---------------------------------------------------
> Smith, Jane, 1
> Jones, Mark 2

Is there any special business requirement that calls for this? If it's
only because you want it to look pretty, forget about it. It's not
worth the pain. That is, there is no simple statement to run. Further-
more, of there are other tables referencing this table, you will have
to update the referencing tables.

It is also worth pointing out that it lies in the design of the IDENTITY
feature that you can get gaps. A failed insert, will still consume a
value. If you want a contiguous series, you should roll your own and
not use IDENTITY. (But beware that this will not scale as well.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog <esquel@sommarskog.se> wrote in
news:Xns95581D3A3419Yazorman@127.0.0.1:

> DBA (kaylisse@yahoo.com) writes:
>> I have an identity field on a table in SQL Server. The identity seed
>> is 1 and the identity increment is 1. If I remove a record from this
>> table, the identity sequence is broken. For example:
>>
>> Table contents prior to record delete:
>> Fname(varchar), Lname (varchar), row_id (identity)
>> --------------------------------------------------
>> Smith, Jane, 1
>> Smith, Tom, 2
>> Jones, Mark 3
>>
>> Delete from mytable where row_id = 2
>>
>> Fname(varchar), Lname (varchar), row_id (identity)
>> ---------------------------------------------------
>> Smith, Jane, 1
>> Jones, Mark 3
>>
>> How can I re-set the identity field so that there are no gaps in the
>> sequence?
>>
>> For example, I want to update the sequence after the delete so that
>> records look like this:
>> Fname(varchar), Lname (varchar), row_id (identity)
>> ---------------------------------------------------
>> Smith, Jane, 1
>> Jones, Mark 2
>
> Is there any special business requirement that calls for this? If it's
> only because you want it to look pretty, forget about it. It's not
> worth the pain. That is, there is no simple statement to run. Further-
> more, of there are other tables referencing this table, you will have
> to update the referencing tables.
>
> It is also worth pointing out that it lies in the design of the
IDENTITY
> feature that you can get gaps. A failed insert, will still consume a
> value. If you want a contiguous series, you should roll your own and
> not use IDENTITY. (But beware that this will not scale as well.)
>

If he really wants this effect, how about using a view with a self-join:

create view Mytable_With_Counter
AS
SELECT Fname,
Lname,
count(*) where a.row_id >= b.row_id [rowNum]
FROM myTable

All,
I figured this out! I wanted to re-order the identity field because I am
using that field to iterate through a tbl. I did not want to use a
cursor, so I tried to use the identity field. In the end, I dropped the
identity and I am using an int field now.

I am doing as follows (found this code to re-set the pseudo-"identity"
field on the net):


/*
re-set a field on the table to use for looping tblcontents (alternative
to cursor)
*/
declare @rowid int
set @rowid = 0
UPDATE dbo.MYTABLE SET @rowid = row_id = @rowid + 1

/* loop through tbl */
declare @ct as int, @row_id as int
select @row_id = 1, @mydata as varchar(255)
SELECT @ct = count(*) FROM dbo.MYTABLE
if @ct = 0 begin
print 'No records found'
end
else
begin
while @row_id < @ct + 1
begin
SELECT @mydata = myfield FROM dbo.MYTABLE WHERE row_id = @row_id

select @row_id = @row_id + 1 --move to the next row
end
end

Thanks for your comments!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Christina Starr (kaylisse@yahoo.com) writes:
> I figured this out! I wanted to re-order the identity field because I am
> using that field to iterate through a tbl. I did not want to use a
> cursor, so I tried to use the identity field. In the end, I dropped the
> identity and I am using an int field now.

That is a very poor reason for wanting numbers to be in sequence.

First of all, iterating over a table is something you should avoid. As
much as possible try to use set-based queries that operates on the entire
dataset. The difference in performance can be *huge*. But it should be
said that there are situations where the cost of developement for a
set-based solution if far to expensive in relation to the performance
gain. (The number of rows is modereate, and there exists already a very
complex stored procedure that performs the task for a set of scalar
values.)

Second, if you must iterate, a cursor is often the most effecient solution.
Just make sure that you say INSENSITIVE before CURSOR to avoid nasty
surprises.

Third, you really are dead-set on using a non-cursor iteration, the idiom
is:

SELECT @id = 0; -- Assuming id starts at 1.
WHILE 1 = 1
BEGIN
SELECT @id = MIN(id) FROM tbl WHERE id > @id
IF @id IS NULL
BREAK

SELECT @col1 = col1, @col2 = col2, ... FROM tbl WHERE id = @id
-- Do stuff.
END

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks for the pointers and the alternative solution!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!