Possible to avoid the FILLING of GAP (created by deleting a record) in the Identity Column

Hi all,

(I am using SQL Server 2005)

I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.

Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!

I do not want to re-use the already used Identity value.

I just want to have the last CustomerID to be higher that all the previous ones.

Is there any way to do this?

Thanking you in advance,

Tomy

How are you deleting this record? If you follow this example through, you'll see that new records will get a new ID:

-- CREATE THE TABLECREATE TABLE CUSTOMER (idint IDENTITY(1,1),name varchar(50))-- INSERT TWO TEST RECORDSINSERT CUSTOMERVALUES ('Mark')INSERT CUSTOMERVALUES ('Fred')-- SHOW THE TWO TEST RECORDSSELECT id,name FROM CUSTOMER-- DELETE THE SECOND RECORDDELETE CUSTOMERWHERE id = 2-- INSERT A NEW RECORDINSERT CUSTOMERVALUES ('Joe')-- SHOW ALL THE RECORDS (THE NEW CUSTOMER WILL HAVE A NEW ID)SELECT id,name FROM CUSTOMER-- DROP THE TABLEDROP TABLE CUSTOMER

|||

Hi,

I inserted the records through program, and later, I deleted, the records manually using Visual Development Studio Manager. Later, when I inserted new records programatically, I found that even the deleted IDs were used.

Thanks

Tomy