Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: 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!


    //Peter

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Problem with IDENTITY column

    hi 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •