473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Server Identity Field

DBA
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
Jul 20 '05 #1
5 8168
DBA (ka******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog <es****@sommarskog.se> wrote in
news:Xn*********************@127.0.0.1:
DBA (ka******@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
Jul 20 '05 #3
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!
Jul 20 '05 #4
Christina Starr (ka******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
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!
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Phil W | last post by:
Hi all, Am having a bit of trouble with the @@identity field - I probably just have that friday feeling and am missing off something obvious, but the below code brings back am empty identity...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
4
by: Edward | last post by:
Access 2k -> SQL Server 2k My client has an app that is A2k FE with A2k BE. They have asked me to move the BE to SQL Server. I have a bit of experience with SQL Server, and I'm happy with...
10
by: Thomas Richards | last post by:
I have two SQL Server 2000 machines. The same file is sent nightly to each server and a stored proc uses BULK INSERT to load it into a staging table for processing. Once I've bcp'ed it in, I put...
5
by: grzes | last post by:
MS SQL Server 2000. My case is: I have the table T with primary key calling __recid int without identity property. This table includes a lot of records (about 1000000). I need to convert __recid's...
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
3
by: Jacek Jurkowski | last post by:
My application is using an identity field of an SQL Server. Identity field called "ID" is a Primary Key too. The problem is when i add some record to the table SQL Server is assigning it sime new...
3
by: Dan | last post by:
I'm writing a record from an asp.net page to SQL Server. After the insert I'm selecting @@identity to return the ID of the record that I just wrote. It worked fine until I typed a semicolon into...
15
by: Marcus | last post by:
I created a VB.Net 1.1 application that iterates through all the tables in any basic Access 2000 database passed to it and generates the same table structure in a SQL Server Express database. The...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.