更改 SQL Server 中的身份种子（永久！）
有没有办法永久更改身份列的身份种子？使用 DBCC CHECKIDENT 似乎只是设置了最后一个值。如果表被截断，所有值都会重置。
dbcc checkident ('__Test_SeedIdent', reseed, 1000)
select name, seed_value, increment_value, last_value
from sys.identity_columns
where [object_id] = OBJECT_ID('__Test_SeedIdent');
返回
name seed_value increment_value last_value
-------------------------------------------------
idIdent 1 1 1000
我希望
alter table dbo.__Test_SeedIdent alter column idIdent [int] identity(1000,1) NOT NULL
存在类似的语法。
是否有必要创建一个新列、移动值、删除原始列并重命名新列？
Is there any way of changing the identity seed for an identity column permanently? Using DBCC CHECKIDENT just seems to set the last_value. If the table is truncated all values are reset.
dbcc checkident ('__Test_SeedIdent', reseed, 1000)
select name, seed_value, increment_value, last_value
from sys.identity_columns
where [object_id] = OBJECT_ID('__Test_SeedIdent');
returns
name seed_value increment_value last_value
-------------------------------------------------
idIdent 1 1 1000
I was hoping that some syntax like
alter table dbo.__Test_SeedIdent alter column idIdent [int] identity(1000,1) NOT NULL
would exist.
Is it necessary to create a new column, move the values across, drop the original column and rename the new?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
来自联机丛书：
“要更改原始种子值并重新播种任何现有行，您必须删除标识列并重新创建它并指定新的种子值。当表包含数据时，标识号将添加到具有指定值的现有行中。不保证种子值和增量值的更新顺序。”
From Books Online:
"To change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. The order in which the rows are updated is not guaranteed."
MSSQL 不允许您通过 TSQL 轻松添加或更改现有列上的标识。您必须删除该列并重新添加它。不用说，这可能会破坏 FK 关系。您可以直接在企业管理器中进行。但是，如果您必须对很多列执行此操作，那就不会有趣了。
是的，不要忘记修复/更新与该列相关的所有索引、外键关系等
MSSQL does not allow you to add or alter an Identity on an existing column via TSQL very easily. You would have to drop the column and re-add it. Needless to say this can play hell with FK relations. You can do it directly in the enterprise manager. However that won't be fun if you have to do this to a LOT of columns.
Yup, and don't forget to fix/update all indexes, foreign key relationships, etc. that are tied to that column
您可以使用
DBCC CHECKIDENT('tablename', RESEED, Seedvalue)示例：
DBCC CHECKIDENT('Customers',RESEED, 1350)再次运行
DBCC CHECKIDENT('Customers')以检查当前种子值是否已设置。但是，正如前面的答案中提到的，这不会更改存储在标识列中的现有值。它只会更改种子值，因此插入的下一行将从该值开始。身份增量保持不变（未更改）并且不能使用 DBCC 进行更改。
You can use
DBCC CHECKIDENT('tablename', RESEED, seedvalue)example:
DBCC CHECKIDENT('Customers',RESEED, 1350)run
DBCC CHECKIDENT('Customers')again to check if current seed value was set.However as mentioned in previous answers this will not change existing values stored in the identity column. It will only change seed value so the next row that is inserted will start with that value. Identity increment remains same (not changed) and can not be changed with DBCC.
实际上，在企业管理器中，当您向现有表添加 ID 列（或将 INT PK 字段更改为 INT PK ID）时，它会在幕后执行此操作。
Actually in Enterprise Manager, when you add an ID column to an existing table (or change an INT PK field to an INT PK ID), it does this behind the scene.