How to reset/restart primary key in SQL Server in T-SQL?

Question

I have a T-SQL database in which there is table that suddenly got a huge jump in primary keys 5 -> 1005. Is there any way to reset PK back to eg. 6?

I have tried following:

USE [database name]
GO
ALTER SEQUENCE [schema name].[table name].[pk name]
    RESTART WITH 6
GO

But it returns error 'CREATE SEQUENCE' does not allow specifying the database name as a prefix to the object name.

I have tried supposedly all the sensible variations of the query but without luck. Is there any other way to restart or reset the PK?

Caring about a gap in primary key values is indicative of a design flaw. — Eric Brandt, Aug 05 '20 at 13:01
I agree but the cause was eventually SQL Server "feature": https://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is — codevictory, Aug 05 '20 at 13:13

score 1 · Accepted Answer · edited Aug 05 '20 at 12:50

1

The important thing to understand is that the sequence does not belong to the table - it isn't part of it. Don't specify the table, just the schema and sequence name.

USE [database name]
GO
ALTER SEQUENCE [Schema Name].[Sequence Name]
    RESTART WITH 6
GO

edited Aug 05 '20 at 12:50

Preben Huybrechts

5,853
2
27
63

answered Aug 05 '20 at 12:48

Northbank75

46
2

score 0 · Answer 2 · answered Aug 05 '20 at 12:47

0

If you use an identity column you can use

DBCC CHECKIDENT ('[schema_name].[table_name]', reseed, 0)

See the documnetation for more info.

answered Aug 05 '20 at 12:47

Preben Huybrechts

5,853
2
27
63

How to reset/restart primary key in SQL Server in T-SQL?

2 Answers2