时间戳

首页 > 解决方案 > 在不删除所有行的情况下重置 IDENTITY 列

sql-server - 在不删除所有行的情况下重置 IDENTITY 列

问题描述

我有下表

CREATE TABLE [dbo].[MyTable](
    [Name] NVARCHAR(200) NOT NULL,
    [Surname] NVARCHAR(200) NOT NULL,
    [Permanent] [bit] NULL,
    [Idx] [bigint] IDENTITY(1,1) NOT NULL
 CONSTRAINT [MyTable] PRIMARY KEY CLUSTERED 
(
    [Idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

包含 1000 行 [Permanent]=1 和 500.000 行 [Permanent] NULL

此外，我有一个执行以下步骤的存储过程（称为MySP）：

  1. 从 [MyTable] 中删除 [Permanent] 为 NULL
  2. （大量计算）
  3. 将第 2 点的结果插入 [MyTable]

MySP 每天都在运行，因此 [Idx] 的数量增加得非常快，因为每天有 500.000 行被删除和插入。我的目标是，在 SP 执行期间，重置列 [Idx] 的值以保持 [Idx] 的最大数量尽可能低。

第一次尝试

我尝试在 MySP 末尾使用以下查询进行更新，但系统给了我一个（正确的）错误。

UPDATE A
SET A.[Idx]=B.[Idx_new]
FROM [dbo].[MyTable] A
INNER JOIN (
    SELECT [Idx],ROW_NUMBER() OVER (ORDER BY [Idx]) AS [Idx_new]
    FROM [dbo].[MyTable]
) B
ON A.[Idx]=B.[Idx]

第二次尝试 阅读以下两个问题/答案后

我在 MySP 的末尾添加了以下内容

DBCC CHECKIDENT ('MyTable', RESEED, 1);

但这在 [MyTable] 中也不起作用，与两个问题的情况不同，保留了一些行，因此 [Idx] 不是唯一的具体风险，并且不好，因为 [Idx] 是我的主要钥匙。

如何重置标识列值以及仍保留在 [MyTable] 中的行？

标签： sql-serversql-server-2005primary-keysql-server-identity

解决方案

使用 @Killer Queen建议，我已经解决了使用这段代码的问题，它在 the之后找到了MAX([Idx])of ，并在 new 之前重新植入了身份。MyTableDELETEINSERT

它之所以有效，是因为带有[Permanent]=1的行是插入表中的第一行，因此它们的[Idx]值从 1 开始并且非常低。

DELETE 
FROM [MyTable]
WHERE [Permanent]!=1 OR [Permanent] IS NULL

DECLARE @MaxIdx As bigint
SET @MaxIdx = (SELECT ISNULL(MAX([Idx]),0) FROM [MyTable])

DBCC CHECKIDENT ('MyTable', RESEED, @MaxIdx);

推荐阅读