如何在不插入值的情况下增加标识列？
首先，我知道 这个问题没有得到回答，因为OP真正想做的不是增加身份列，
我有一个当前种子值为x的身份列，我想重新播种它到 x+1 （即我希望我的身份列直接从 x 跳转到 x+2。
我知道我可以使用以下 command
create procedure IncrementSeedValue
(
@TableName varchar(255),
@IncrementValue int
)
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
declare @v bigint
select @v = IDENT_CURRENT(@TableName)+@IncrementValue
DBCC CHECKIDENT (@TableName, RESEED, @v )
COMMIT TRANSACTION;
go
但是，我有几个问题：
- 这里的隔离级别“可序列化”是否足够？
- 如果我使用 SQL Server，这会导致问题吗？镜像还有
- 其他我应该注意的陷阱吗？
First, I'm aware of this question which didn't get answered because what the OP was really trying to do was'nt incrementing an identity column
I've got an identity column with a current seed value of x, and I would like to reseed it to x+1 (ie I want my identity column to jump directly from x to x+2.
I know I can do that using the following command
create procedure IncrementSeedValue
(
@TableName varchar(255),
@IncrementValue int
)
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
declare @v bigint
select @v = IDENT_CURRENT(@TableName)+@IncrementValue
DBCC CHECKIDENT (@TableName, RESEED, @v )
COMMIT TRANSACTION;
go
However, I've got a few questions :
- Is the isolation level "serializable" adequate here?
- Would that lead to problem if I'm using SQL Server mirroring
- Are there other pitfalls I should be aware of ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
每个 INSERT 都会消耗一个 IDENTITY 值。
我要么插入一个值并立即删除它，要么插入该值并发出回滚。
无论哪种情况，IDENTITY 值都会被消耗。
我认为 DBCC 命令不应该在“正常”代码中使用，而应该仅用于管理任务。
或者，如果您可以使用 SET IDENTITY_INSERT ON 并计算跳过值的下一个值应该是什么。
An IDENTITY value is consumed for every INSERT.
I would either INSERT a value and immediately DELETE it, or INSERT the value and issue a ROLLBACK.
In either case, the IDENTITY value will be consumed.
I don't feel that DBCC commands should be used in "normal" code and should be reserved for administrative tasks only.
Or, if you can use SET IDENTITY_INSERT ON and calculate what the next value with the skipped value should be.