主页 / dba / 问题 / 169180
Accepted
Alexei
Asked: 2017-04-06 02:28:06 +0800 CST

如何减少标识值以避免整数溢出？

  • 772

我有一些叶表（对它们没有 FK），其中包含数十万条记录，这些记录用于使用 Entity Framework ORM 同步一些外部数据。这涉及一些 DELETE，然后是 BULK INSERT。

对于大多数表，一些旧值可能会永远保留，因此我不能将 SEQUENCEs 与 CYCLE 一起使用，正如其中一条评论所建议的那样。

一种影响是身份值会随着时间的推移不断增加，我希望能够降低它们的值。

这个问题及其答案解释了身份值无法更新，即使identity_insert是在表上。

一种快速的方法是将所有数据传输到缓冲区表并通过重命名执行切换。类似于以下内容：

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);  

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.    

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)    

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.  

-- this should be replaced with commit when not in test mode
rollback

我有能力在夜间进行这种操作，而且我的数据量似乎只需要几秒钟（本例为 4 秒）。

问题： 是否有任何方法可以避免执行完整数据复制以获取标识列的较小值？或者这是在我的上下文中最好的方法之一（合理的数据量并且能够锁定一些表几秒钟）。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    您可以尝试简单地删除并重新添加 Identity 列而不复制整个表。这与您尝试使用问题中的代码具有相同的效果。根据问题中的“某些旧值可能永远保留”语句，这两种方法都没有真正处理保留现有行，但是对于问题中的代码适用的任何情况，以下内容也应该有效。

    您还应该考虑从最小值开始标识范围。从 1 开始会给你超过 21 亿个值，所以从最小值（或接近它）开始会给你 42 亿个值。因此，将来您不太可能需要这种重新播种操作。

    设置

    -- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
  ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
  SomethingElse UNIQUEIDENTIFIER NOT NULL,
  SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
  SELECT NEWID(), so1.[name] + N'~' + so2.[name]
  FROM   master.sys.all_columns so1
  CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

    删除并重新创建身份 PK

    BEGIN TRY
  BEGIN TRAN;

  ALTER TABLE dbo.IdentityTest
    DROP CONSTRAINT [PK_IdentityTest];

  ALTER TABLE dbo.IdentityTest
    DROP COLUMN [ID];

  ALTER TABLE dbo.IdentityTest
    ADD [ID] INT
    IDENTITY(-2140000000, 1)
    NOT NULL
    CONSTRAINT [PK_IdentityTest]
      PRIMARY KEY;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;
    • 2

  2. 虽然（如评论中所见），还有其他潜在的选择，但似乎是这个：

    • 作品;
    • 不需要大停电；
    • 不需要更改您的应用程序；
    • 如有必要，甚至可以自动化。

    我想这可能是你最好的选择。这是我会用的那个。

    • 1

  3. 您问“是否有任何方法可以避免执行完整数据复制以获取标识列的较小值？” 要查找序列中是否存在间隙，需要读取每一行（或该列上的索引）。要填补空白，需要对超出空白的每一行进行更新。（因为你不能更新一个 IDENTITY 这变成了一个删除，然后是一个插入，在两者之间有一些 reseed jiggery-pokery。这感觉它可能比“批量表复制”方法慢，尽管这样做的理由让我难以理解并且可能取决于预读和磁盘访问算法。）假设正在删除的是较旧的数据，它将具有较低的标识值，因此每次都会更新表的大部分内容。具有顺序读取和写入的完整副本听起来会更有效率。

    一种方法是对表进行分区。在工作表中重新设定身份。切换出旧数据，切换到工作表中。停机时间将以（毫秒）秒为单位。

    免责声明：我没有测试过，所以不确定它会如何反应。必须考虑在准备工作表时发生的写入。也许两个活动分区按交替计划进行维护？

    • 0

相关问题