<<identity field>>

hi friends,
i have an identity column in my table.(e.x : id )
and it's identity increment is 1.
when i insert a row the id field is 1, and in next record the field is 2....
now , i delete second record(id=2)
and now when i insert a record again , the id column is 3.
i want to record be 2 instead 3.

plz help me.
thanks

This cannot be accomplished "as standard"

When you delete a record, add the id to a "secondhand id" table, then, when you want to insert a new record, see if there is a secondhand id.

The rationale for this approach is that there are no fast methods to iterate the table with id's that may be free when there are large numbers of records

|||

well you can do it but it would not be the best way to do programming......... there can be 2 ways.

Method 1 :

Forget about identity columns.
insert the record by yourself using insert command.
before using insert command retrieve the id (as in your case) of the last record of the table. you can do it like this.
str=select id from <tablename> orderby id
get the results by using this command into a datareader and store the last value in a variable
dim last_id as integer
while dr.read
last_id=dr(0)
end while.
now insert the last_id as the id in your insert statement...

Method 2:

you can reseed(reset) the identity value when you delete a record. i mean to say when you delete a record just reset the identity column to that id(or might be one previous id) for reseting the coulmn see the link below.
http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

hope this could be helpful