在SQL Server中重新排序身份主键 [英] Reordering Identity primary key in sql server

问题描述

是的，我非常清楚后果.但是我只想重新排序.从1到结束.

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 ?

它是集群主键索引

像这样重新排序

First record Id 1 
second record Id 2

主键为Int

推荐答案

1. 下降PK约束
2. 丢弃身份"列
3. 重新创建身份列
4. 重新创建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

这篇关于在SQL Server中重新排序身份主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！