Resetting primary key seed?

How do I reset the PK seed in auto increment ID column?

Page 1 of 1

5 Replies - 3792 Views - Last Post: 20 November 2008 - 04:10 AM Rate Topic: -----

#1 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Resetting primary key seed?

Post icon  Posted 13 November 2008 - 02:40 AM

I have this table (simplified but enough to get my point across) that has the key column (named ID) as auto increment integer seed 1 no duplicates.

CREATE TABLE [dbo].[DM5FelLogg](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[tid] [datetime] NOT NULL,
	[felUppstod] [bit] NULL,
	[felKod] [int] NULL,
	[felText] [varchar](100) NULL,
	[maskinID] [int] NULL,
 CONSTRAINT [PK_DM5FelLogg] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]



Every time a record is added the ID is incremented. If I delete all records and start over the numbering does not start from 1 again but one more than the last post earlier.

How do I reset the ID so it starts from 1 again?

What I want to do is to insert records and then delete them all. When inserting new records into the now empty table I want the ID to start from 1 again, like the very first time. Is this doable without deleteing the table and recreating it?

I am aware that there are several reasons not to meddle with the primary key, but I also have a reasons to do it. (One reason that could be thought of is that the PK is reaching it's upper limit).

This leads me to another question:
If (for some reason) the PK is very short, e.g. shortint and one insert needs a value > (max of shortint), what will happen?

Regards
/Jens

This post has been edited by jens: 13 November 2008 - 02:46 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Resetting primary key seed?

#2 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Resetting primary key seed?

Posted 13 November 2008 - 05:29 AM

It can be done, but differently per database brand.
What kind of DB is it?
Was This Post Helpful? 0
  • +
  • -

#3 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: Resetting primary key seed?

Posted 13 November 2008 - 05:53 AM

MS SQL server 2000, I'd also like to know how to do it on MS SQL server 2005 and 2008 if possible.

Thanks
/Jens
Was This Post Helpful? 0
  • +
  • -

#4 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Resetting primary key seed?

Posted 13 November 2008 - 07:45 AM

check out http://msdn.microsof...y/ms176057.aspx
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Resetting primary key seed?

Posted 13 November 2008 - 08:08 AM

truncating the table will reset the auto increment id. but of course it will also wipe all the data from the table.

TRUNCATE TABLE Table1

Was This Post Helpful? 1
  • +
  • -

#6 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: Resetting primary key seed?

Posted 20 November 2008 - 04:10 AM

Thanks guys! @eclipsed - promptly solved it all. :) /Jens
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1