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
 
No comments:
Post a Comment