DBCC CHECKIDENT RESEED-是否需要新值? [英] DBCC CHECKIDENT RESEED -- is new value required?

问题描述

我阅读的有关播种的所有文档都提出了类似的建议:

All the documentation I read about reseeding suggests something along the lines of:

1. SET @maxIdentityValue = (SELECT MAX(id) FROM tablename)
2. 运行DBCC CHECKIDENT('tablename', RESEED, @maxIdentityValue)

1. SET @maxIdentityValue = (SELECT MAX(id) FROM tablename)
2. run DBCC CHECKIDENT('tablename', RESEED, @maxIdentityValue)

然后 在我看来，只需要一个简单的DBCC CHECKIDENT('tablename', RESEED)，它将自动从表中确定正确的标识值，而无需提供最大值.

And yet it appears to me that a simple DBCC CHECKIDENT('tablename', RESEED) is all that's needed, and it will automatically determine the correct identity value from the table without supplying a max value.

是否有理由(性能或其他方面)优先使用MAX提取值?

Is there a reason (performance or otherwise) that extracting the value using MAX first is preferred?

Pi带问题:我需要重新设置种子的原因是因为我正在使用复制，并且每次数据库复制运行时身份始终设置为Null.我究竟做错了什么?如何为每个表维护正确的身份种子?

Piggyback question: the reason I need to reseed is because I'm using replication and identities keep getting set to Null each time the database replication runs. What am I doing wrong? How can I maintain the correct identity seed for each table?

目前，我没有使用最大值.这是我正在使用的存储过程(我使用sys.columns上的查询生成它，然后将其剪切并粘贴到新的查询窗口中.更麻烦，更慢，更不优雅，但是我对存储过程不是很熟悉并且不想使用动态SQL查询):

For now I'm not using the max value. This is the stored procedure I'm using (I generate it using a query on sys.columns and then just cutting and pasting each into a new query window. Messier, slower, less elegant, but I'm not very familiar with stored procedures and don't want to use dynamic SQL queries):

declare @seedval integer
declare @maxval integer
declare @newval integer
set @seedval = (select ident_current('mytable'));
set @maxval = (select MAX(id) from mytable);
if @maxval > @seedval or @seedval is NULL
BEGIN
    print 'Need to reseed: max is '  + cast(@maxval as varchar) + ' and seed is ' + cast(@seedval as varchar) 
    dbcc checkident('mytable', RESEED);
    set @newval = (select ident_current('mytable'));
    print 'Max is ' + cast(@maxval as varchar) + ' and seed is ' + cast(@newval as varchar) 
END 
ELSE
    print 'No need to reseed'; 

推荐答案

MSDN ，只需使用以下内容就足够了:

As it is stated in MSDN, it is fairly enough to use just:

 DBCC CHECKIDENT('tablename', RESEED)  

大多数情况下，但是在以下两种情况下它不起作用:

most of the time, however there are these two conditions where it will not work:

• 当前标识值大于表中的最大值.
• 从表中删除所有行.

在其中必须遵循您提到的方式(选择max(id)和其余的方式)，所以为什么首先要打扰? :)

in which you have to go with they way that you mentioned (select max(id) and the rest), so why bother in the first place? :)

这篇关于DBCC CHECKIDENT RESEED-是否需要新值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！