SQL | Reset Identity Seed in Table Column

September 1, 2009

A helpful tip for resetting your Identity Seed Column Property. . .

Open Table > Design Mode > Select Column with Indentity Specification of ‘Yes’ and review the following settings:

  • (Is Identity) = Yes
  • Identity Increment = [user-defined value] (e.g. ‘1’)
  • Identity Seed = [user-defined] (e.g ‘1’)

Now for the actually Management Studio Statements. . . Open New Query Window :

  1. DBCC CHECKIDENT ([table name], NORESEED) — Will check identity information : current identity value ‘x’, current column value ‘1’.
  2. DBCC CHECKIDENT ([table name], RESEED, [numeric value]) — Will set the Identity Seed to [numeric value]
  3. Run Step # 1 to validate Step # 2 Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: