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?

There's a good reason it works like that, it's so that you don't give two different things the same ID over time. Imagine you have a receipt database, you delete a few receipts by accident, a new customer comes in and now two customers have a receipt with the same ID?! Sorcery. — Liam George Betsworth, May 15 '12 at 13:02
You're looking to `RESEED` the index. See here for syntax and issues you will encounter: http://stackoverflow.com/a/4165362/1016183 — Morphed, May 15 '12 at 13:05
@LiamGeorgeBetsworth so even tho i think it looks wrong, its better to have it like that from a database guys point of view? — Pomster, May 15 '12 at 13:06
@Pomster I'll agree with you, it doesn't look tidy, but that's how it should be. No two ways about it. It's better to be correct than tidy! — Liam George Betsworth, May 15 '12 at 13:10
No i had an plan but i guess i should change it as all the comments say i should not want to do this. But thanks all and +1 for @LiamGeorgeBetsworth — Pomster, May 15 '12 at 13:11

YvesR · Answer 1 · 2012-05-15T13:09:49.150

4

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 :)

edited May 15 '12 at 13:09

answered May 15 '12 at 13:00

YvesR

5,922
6
43
70

But i don't want to lose all my data. Can i not do it and keep my data? – Pomster May 15 '12 at 13:01
I want to be doing this all though C# code every time i call my delete stored procedure. Is there no other way? I need it to auto shit after i delete your method looks like a once of fix. – Pomster May 15 '12 at 13:03
@Pomster - do you *always* want to restart numbering at 1? Even if you delete record #3 and record #1 still exists? – dj18 May 15 '12 at 13:06
Still don't get it why you want to do this. As far as I know there is no way to "modify" values exept you turn off the auto value temporary and insert new values with "old auto id'S" manually (Using: SET IDENTITY_INSERT tablename OFF/ON). – YvesR May 15 '12 at 13:07
So the correct answer is i don't want to do this!? i should leave it as it is? – Pomster May 15 '12 at 13:08
1

@Pomster Correct, don't touch this Id. Add another ID-Column for your own porpuses that you can resort with a update function to resort your rows and order by this column if needed. – YvesR May 15 '12 at 13:10

score 4 · Answer 2 · answered May 15 '12 at 13:14

A quick analogy of why you don't want to do this:

Imagine if everytime someone canceled their phone service, the phone company went back and reassigned everyone's numbers above the canceled number to remove the gap. This would cause undo headache for all involved.

A PK should not only be unique, but also constant for that row. Changing the identity of the row causes problems.

If you want to fill in the gaps as new records are added, you can either use IDENTITY INSERT, or remove the identity from the table and generate your own id each time a record is inserted.

And, if for some reason that you haven't communicated yet you still want to do this - the RESEED answers are the way to go.

score 3 · Answer 3 · edited May 15 '12 at 22:33

3

You have to use RESEED:

DBCC CHECKIDENT (table_name, RESEED);

use this in a trigger after delete.

edited May 15 '12 at 22:33

glenstorey

5,134
5
39
71

answered May 15 '12 at 13:12

Amogh Rai

159
1
7

This is not an answer as he want to resort all, not reseed the last value. – YvesR May 15 '12 at 13:13
On delete check the number of records being deleted, get the index value substract the value and reset the index using this code DBCC CHECKIDENT(, RESEED, value) – Amogh Rai May 15 '12 at 13:14
If you delete only the last record this might work in theory, but even his example showed he delete older rows after many new records added. – YvesR May 15 '12 at 13:17
Oh yes i think i miss understood the question. Don't change your primary key values for existing records you will have data issues. – Amogh Rai May 15 '12 at 13:23

How to auto Shift primary keys to start from 1 again

3 Answers3