Identity Seed

  • Hello,

    A table I'm working with is created with an Identity Seed of 39999999. Then it is loaded using source data from a Mainframe Application which has a unique 9 digit number which is loaded as the field with the identity. I'm doing the following in a DTS Package:

    1) SET IDENTITY_INSERT mytable ON

    2) Load a .txt file to the table. Each record in the .txt file contains a unique number and is used as the id.

    3) SET IDENTITY_INSERT mytable OFF

    4) DECLARE @iIdentitySeed INT

    SELECT @iIdentitySeed = MAX(id) FROM

    mytable

    DBCC CHECKIDENT(mytable,

    reseed, @iIdentitySeed)

    Everything is working fine. When a

    new record is inserted into mytable via an ASP application, the id number created is the max(id) + 1.

    My consern is that in EM the Identity Seed is still 39999999 where I thought it would be the max(id), which is 100227919.

    Did the Identity Seed get "reseeded" or is SQL Server using the max id in the table and overriding the Identity Seed of 39999999?

    I see that as records are added to a table the Identity Seed does not change. I thought it would when the "RESEED" was used.

    Thanks

  • dear sdidomenico,

    Once u set the IDENTITY_INSERT ON , and inserted the records into it, the next value would be the max+1. It would not take old value, though EM shows design time seed.

    Rajendar

    erajendar


    erajendar

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply