Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Unanswered: Script to reset the "Identity Seed" and "Identity Increment" values

    hi,

    Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.

    I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.

    The table can't be droped as it is part of replication.
    Is there a work around for this ?
    Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?

    Any help whould be greatly appreciated.

    Thanks,
    Prashanth Reddy

  2. #2
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17

    Re: Script to reset the "Identity Seed" and "Identity Increment" values

    Originally posted by Prashanth
    hi,

    Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.

    I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.

    The table can't be droped as it is part of replication.
    Is there a work around for this ?
    Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?

    Any help whould be greatly appreciated.

    Thanks,
    Prashanth Reddy
    You have 2 options that spring to mind:

    1. You can use TRUNCATE TABLE to accomplish this, but you will lose all data and as its a non-logged operation nothing will be written to the transaction log. I should add that if the table is referenced by a foriegn key constraint then you will not be able to use this method.

    Usage: TRUNCATE TABLE tablename


    2. The other option is to use DBCC CHECKIDENT to reset the seed:

    Usage: DBCC CHECKIDENT (tablename, RESEED, 0)


    macka.
    Last edited by macka; 01-23-03 at 12:53.

  3. #3
    Join Date
    Jan 2003
    Posts
    5
    thanks for the response.

  4. #4
    Join Date
    Jan 2003
    Posts
    5
    thanks for the response.

    I have patient table with PatientID as auto increment field
    with
    "Identity Increment" as 1 and
    "Identity seed" as 1
    I can chane Identity Seed using
    DBCC checkident (patient,reseed,4)
    How do i change the "Identity Increment" value to 10?


    Prashanth

  5. #5
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    The following script should work. In this example it resets the seed to 4 and increment to 10.

    If the table is referenced by foreign key constraints then you will need to drop them before this script is run and add them back after it completes.

    Code:
    ALTER TABLE Demo
    DROP CONSTRAINT PK_Demo
    
    ALTER TABLE Demo
    DROP COLUMN DemoID
    
    ALTER TABLE Demo 
    ADD DemoID INT IDENTITY(4,10)
    
    ALTER TABLE Demo ADD CONSTRAINT
    	PK_Demo PRIMARY KEY CLUSTERED 
    	(
    	DemoID
    	) ON [PRIMARY]
    macka.

Posting Permissions

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