Reordering Identity primary key in sql server

Question

Yes i am very well aware the consequences. But i just want to reorder them. Start from 1 to end. How do I go about reordering the keys using a single query ? It is clustered primary key index Reordering like ``` First record Id 1 second record Id 2 ``` The primary key is Int

Accepted Answer

- Drop PK constraint

- Drop Identity column

- Re-create Identity Column

- Re-Create PK

```
USE Test
go

if(object_id('IdentityTest') Is not null)
drop table IdentityTest

create table IdentityTest
(
Id int identity not null,
Name varchar(5),
constraint pk primary key (Id)
)

set identity_insert dbo.IdentityTest ON
insert into  dbo.IdentityTest (Id,Name) Values(23,'A'),(26,'B'),(34,'C'),(35,'D'),(40,'E')
set identity_insert dbo.IdentityTest OFF

select * from IdentityTest

------------------1. Drop PK constraint ------------------------------------ 
ALTER TABLE [dbo].[IdentityTest] DROP CONSTRAINT [pk]
GO
------------------2. Drop Identity column -----------------------------------
ALTER table dbo.IdentityTest
drop column Id
------------------3. Re-create Identity Column -----------------------------------
ALTER table dbo.IdentityTest
add Id int identity(1,1)
-------------------4. Re-Create PK-----------------------
ALTER TABLE [dbo].[IdentityTest] ADD  CONSTRAINT [pk] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)

--------------------------------------------------------------
insert into  dbo.IdentityTest (Name) Values('F')
select * from IdentityTest
```

Reordering Identity primary key in sql server

Solution

Problem