如何将列更改为 IDENTITY 列并保留数据?
我有一列是 IDENTITY(1,1).所有行均已连续编号.我没有添加或删除任何行.我删除了此列上的标识属性.现在，我想要它回来.我想我可以将其重置为现有表中种子 = 最大数字的身份.
I had a column which was IDENTITY(1,1). All rows had been numbered consecutively. I did not add or remove any rows. I removed the identity property on this column. Now, I want it back. I think i can reset it to identity with seed = biggest number in existing table.
如果我这样做会不会有什么问题?
Could any problems arise if I do this ?
来自 MSDN SQL Server 论坛 - 您有 2 个选项:
From MSDN SQL Server forum - You have 2 options:
- 创建一个具有身份的新表 &删除现有表
- 创建一个带有身份的新列 &删除现有列.
*但是当这些列有任何约束/关系时要特别小心
*But take special care when these columns have any constraints / relations
如 MSDN 文档中所述 关于在部署和架构更新期间保留数据的注意事项:
As stated in MSDN documentation about considerations for preserving data during deployment and schema Updates:
更改表上标识列的属性，例如标识种子 - 需要数据移动"，并且在某些情况下，它可能会在执行同步期间导致数据丢失.
Changes to properties of an identity column on a table, such as the identity seed - "Requires data motion" and it can, in some cases, cause data loss during its execution of the synchronization.
在这种情况下的建议:如果部署或更新脚本包含这些类型的更改，您可能需要手动修改它以保留数据"