How to auto Shift primary keys to start from 1 again
database, sql, sql-server, sql-server-2008, stored-procedures
Solution
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 :)
Problem
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?