[jens]

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

[Trogdor]

It can be done, but differently per database brand.
What kind of DB is it?

[jens]

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

[xerxes333]

check out http://msdn.microsof...y/ms176057.aspx

[eclipsed4utoo]

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

[jens]

Thanks guys! @eclipsed - promptly solved it all. /Jens