身份字段跳转 [英] Identity field jumps

问题描述

我们有SQL 2012，所有表都使用了identity字段，当服务器重启时，这个值会跳转。我们在互联网上找到了这个替代方案



https://binary-stuff.com/post/failover-or-restart-results-in-reseed-of-identity- --fix



我们不确定我们是否只需要在Master数据库上保存这个存储过程，或者我们是否还需要创建一个数据库作业来执行每次服务器重新启动时都会这样。

We have SQL 2012, all tables used identity field, when the server is restart , this values jumps. We found this alternative on Internet

https://binary-stuff.com/post/failover-or-restart-results-in-reseed-of-identity---fix

We are not sure if we only have to save this store procedure on the Master database or if we also have to crate a database job in order to execute this each time the server is restart.

推荐答案

这是因为SQL Server缓存了身份的值。如果你愿意，你可以在启动参数中应用t272来防止这种情况，但如果编号很关键，我建议你去序列而不是身份。



但是，我仍然会考虑为什么在身份价值中出现问题首先是一个问题？你真的需要一系列没有洞的价值观吗？ ：）

This happens because SQL Server caches the values for identities. If you want, you can apply t272 in startup parameters to prevent this, but I would suggest going to sequences instead of identities if the numbering is critical.

However, I would still consider why having holes in the identity values is an issue in the first place? Do you really need a serie of values with no holes? :)

这两种方法都不会产生连续编号。只是单调的增加一个。对于内部ID，没关系。如果您需要经济上可接受的连续编号（比方说发票），您需要另一种方法：

0）假设您有发票表

1）使用身份技术ID（TID）的字段（或序列）

2）为连续ID设置不同的字段。 （CID）

3）创建一个包含值的表，并将起始编号从连续ID中放入该单元格中

4）到达状态时您确定您正在处理的实体不会被删除（假设您已完成发票），启动可以发挥作用的交易。请参阅下面的SP。



这是演示数据库：

None of the two methods will yield a continuous numbering. Only a monotonous increasing one. For internal IDs, that's fine. If you need financially acceptable continuous numbering (let's say for invoices), you need an other approach:
0) Let's say you have an invoices table
1) Use an identity field (or a sequence) for technical ID (TID)
2) Have a different field for the continuous ID. (CID)
3) Create a table that has a value in it, and put the starting number form the continuous ID in that cell
4) When reaching the state where you are sure that the entity you are dealing with won't be deleted (let's say you have finished completing the invoice) start a transaction that can do the magic. See the SP below.

Here is the demo database:

USE [_playground_]
GO
/****** Object:  StoredProcedure [dbo].[FinalizeInvoice]    Script Date: 2015.09.08. 21:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FinalizeInvoice] 
	@TID int
AS
BEGIN
	SET NOCOUNT ON;
	SET LOCK_TIMEOUT 100;
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
	BEGIN TRANSACTION 
	BEGIN TRY
		update dbo.CID_sequence set V=V+1 where id=0;
		update dbo.Invoices 
			set CID = (select V from dbo.CID_sequence where id=0) 
			where TID = @TID
		--WAITFOR DELAY '00:00:10' --for demonstration only
		COMMIT
	END TRY
	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber
		ROLLBACK
	END CATCH;
END

GO
/****** Object:  Table [dbo].[CID_sequence]    Script Date: 2015.09.08. 21:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CID_sequence](
	[V] [int] NOT NULL,
	[id] [int] NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Invoices]    Script Date: 2015.09.08. 21:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Invoices](
	[TID] [int] NOT NULL,
	[CID] [int] NULL,
	[customer] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED 
(
	[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

如果您运行以下脚本，您将看到最后一个序列被回滚：

If you run following script, you will see that the last sequence is rolled back:

insert into dbo.Invoices(tid, customer) values (1, 'joe');
insert into dbo.Invoices(tid, customer) values (2, 'bill');
insert into dbo.CID_sequence(id, V) values(0,1);

exec dbo.FinalizeInvoice 1;

begin transaction
exec dbo.FinalizeInvoice 2; --start this as second in parallel 

select * from dbo.Invoices;
select * from dbo.CID_sequence

rollback

select * from dbo.Invoices;
select * from dbo.CID_sequence

delete from dbo.Invoices;
delete from dbo.CID_sequence

如果你取消SP中的wait语句，你把第二个exec放在一个单独的查询窗口中，你启动脚本而不是第二个exec，你会看到，后者将立即失败。实际上在100毫秒后，SP就像这样写了。您可以决定愿意等多久。由于这两个更新应该很快终止，等待那么长时间就足够了。仍然，您需要在应用程序中做好准备，以便在并发CID生成无法获取锁定时处理这种情况。

If you decomment the wait statement in the SP and you put the second exec in a separate query window and you start the script than immediately the second exec, you will see, that the later one will fail instantaneously. Actually after 100ms as the SP is written like this. You can decide how long you are willing to wait. As the two updates should terminate quickly, waiting that long is more than enough. Still, you need to be prepared in the application to handle the situation when the concurrent CID generation can't get the lock.

这篇关于身份字段跳转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！