Post reply

Setting Identity Seed and Identity Increment

  • July 21, 2003 at 4:02 pm

    #159515

    Is it possible to set the Identity Seed and Identity Increment in T-SQL? I'm in a converion phase and I would like to use

    T-SQL rather than T-SQL and EM to set the Identity Seed and Identity Increment after I load a table. The field is the primary index and it is an INT.

    Can I do something like

    "select max(id) from tableX" and then use the result in the T-SQL code to set the Identity Seed? The Identity Increment will be a fixed number of 1.

    What I have been doing is running some SQL like "select max(id) from tableX" and then apply the changes using EM.

    Thanks in advance for your time.

  • July 21, 2003 at 5:42 pm

    #466308

    How about something like this as an example?

    declare @hival int

    select @hival = max(id) from mytable

    dbcc checkident(mytable, reseed, @hival)

    HTH,

    Steve Armistead,

    Database Administration

    Panther Systems Northwest, Inc.

    Vancouver, WA

    Steve

  • July 22, 2003 at 12:08 am

    #466318

    Hi sidomenico,

    quote:

    Is it possible to set the Identity Seed and Identity Increment in T-SQL? I'm in a converion phase and I would like to use

    T-SQL rather than T-SQL and EM to set the Identity Seed and Identity Increment after I load a table. The field is the primary index and it is an INT.

    what about adding this field after you've loaded the table?

    SQL Server will produce a fine consecutive numbering on it.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 22, 2003 at 9:42 am

    #466427

    See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

  • July 22, 2003 at 10:34 am

    #466443

    Yes. DBCC CHECKIDENT with RESEED PARAMETER.

    As in:

    DBCC CHECKIDENT ('MyTable',RESEED, 1)

    G. Milner

  • July 28, 2003 at 11:24 am

    #467330

    Thank you.

  • July 29, 2003 at 2:39 pm

    #467596

    The 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. 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.

Viewing 7 posts - 1 through 6 (of 6 total)

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