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