Identity column issue

[mepancha]

Hi,
I have the following problem with the Identity column. Will be glad if someone could help me solve this issue.

Table : Test
Identity Field Name : No1
Field Name : Name1

Initially I insert 5 records so the value in the field No1 is
1
2
3
4
5

Due to errors I delete records where No1 is 4 and 5 so now I have only records where the value of No1 is 1 , 2 and 3.
I insert 2 records afresh. The value assigned to No1 is 6 and 7 but then I want the value to be 4 and 5. Please let me know if this is possible. If yes, how should I be doing this.

Thanks is advance.

 

[MDXer]

You need to ressed the identity value. Look in BOL for DBCC checkIdent()

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[JayKusch]

The Identity field is a "running/incrementing" number. It remembers the last identity value used and assigns new records with the next value it has.

SO ... it is working exactly as it is suppose to.

Now there are ways to resequence the identity value w/ records already in place. BUT ... if this number is used as a foreign key or something you could break the referential integity of your DB.

You could also dump out the existing records from the table, truncate the table which reset the identity field back to zero, then reimport your records to get the sequence back in line.

Thanks

J. Kusch