Click here to Skip to main content
15,892,839 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted

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? :)
 
Share this answer
 
Comments
Member 10831965 8-Sep-15 13:59pm    
ok, an alternative is Using Sequence, but we wonder if a transaction is rolled back, we lost the secuence?
Zoltán Zörgő 8-Sep-15 14:24pm    
Try it :)
Or read it:
https://msdn.microsoft.com/en-us/library/ff878091.aspx
General Remarks
Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.
Wendelius 8-Sep-15 14:31pm    
Yes, in case of a rollback you will loose the number. But the same happens with identity in case of rollbacks or if rows are deleted...

Again why the numbering is so critical? The idea of automatic numbering is just uniqueness, not that every value exists.
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:
SQL
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:
SQL
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

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.
 
Share this answer
 
v4

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900