query to change the field identity,Identity seed,Identity Increment

• vamshi.sql

  SSCommitted

  Points: 1770

  More actions

  July 23, 2010 at 12:40 pm

  #221641

  Hi can anyone of you please let me know the query to change the field identity,Identity seed,Identity Increment..

  Details are given below..

  Server: USFKL20DB11CI01

  Database: FESD

  Table: dbo. Usernames

  Field: UserID

  Plesae ensure that the following settings are in place:

  Identity = True

  Identity seed = 1

  Identity increment = 1

  UserID field is a Primary Key..I am unable to edit there in the properties dailog box.So,please let me know the query to change this..

  Thanks in Advance..

  Vamshi.

• jeff.mason

  SSCrazy Eights

  Points: 9585

  More actions

  July 23, 2010 at 12:50 pm

  #1197690

  As far as the seed goes, this will report the seed:

  DBCC CHECKIDENT ("FESD.dbo. Usernames ", NORESEED);

  GO

  And this will set the seed to 1:

  DBCC CHECKIDENT ("FESD.dbo. Usernames ", RESEED, 1);

  GO

  However, this will cause errors on later inserts if values greater than 1 exist in the identity and the column is unique in any way.

  Not sure about how to do the increment step. You may have to re-create the column or table to change that.