How to auto Shift primary keys to start from 1 again

Question

I am using a table created like:

CREATE TABLE [dbo].[Tag](
    [TagID] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](200) NOT NULL,
    [TagCount] [varchar](200) NULL,
 CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
(
    [TagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I can never insert a primary key it just auto continues from the last one. So if my last entry had a primary key of 72 then the next entry will be 73.

Now my problem is when i delete a entry. Say now i deleted entry's 3-7 my database will have entry's stored 1,2,8,9 ect.

Is there a stored procedure i could run after my delete to just auto shift my primary keys to be ordered from 1 again?

Answer 1

Use

TRUNCATE TABLE [dbo].[Tag]

This will empty all data and reset the auto value counter.

EDIT: If you want to save old values. create a temporary table, store all data there, truncate your main table and reinsert them. But why you want to do this?

Something like this (do it in a transaction):

CREATE TABLE [dbo].[TagTmp] (
 [Value] [varchar](200) NOT NULL,
    [TagCount] [varchar](200) NULL
)
INSERT INTO TagTmp (Value, TagCount)
SELECT Value, TagCount FROM dbo.Tag
TRUNCATE TABLE dbo.Tag
INSERT INTO Tag (Value, TagCount)
SELECT Value, TagCount FROM dbo.TagTmp
DROP TABLE TagTmp

EDIT2:

If you want to insert a record with a free "old" auto id then use:

SET IDENTITY_INSERT tablename OFF
INSERT INTO tablename (TagId, Value, tagCount) VALUES (3, 'value', 1');
SET IDENTITY_INSERT tablename ON

This will give you the chance to insert records, but I won't suggest it to do :)