Problem with IDENTITY column

Hi all

I have a problem with a table with an IDENTITY column. In order to illustrate my problem I have created a small table according to:

CREATE TABLE [dbo].[W_Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL ,
[Age] [int] NULL ,
[OwnerID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[W_Person] WITH NOCHECK ADD
CONSTRAINT [PK_W_Person] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

When I create two new records, the field ID is assigned the next available value.

So far everything works fine.

THE PROBLEM:
If I insert a record with the following SQL statements:

SET IDENTITY_INSERT W_Person ON

INSERT INTO W_Person (ID, Name, Age, OwnerID)
VALUES (8000, 'John Johnson', 30, 2)

SET IDENTITY_INSERT W_Person OFF

and then deletes the same record. As a next step I tries to insert a new record and let SQL Server generate a new id, the new id is assigned the value of 8001 instead of 3.

Please, can anybody help me on this one!

//Peterhi peterand,

the identity-seed is set to 8000 after your insert. so the next value will be 8001...
if you want to re-seed, take a look at bol "DBCC CHECKIDENT"

markus