I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:
CREATE TABLE [dbo].[PONumbers] ( [PONumberPK] [int] IDENTITY(1,1) NOT NULL, [NewPONo] [bit] NOT NULL, [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(), CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC) );
And a stored procedure created using this script:
CREATE PROCEDURE [dbo].[GetPONumber] AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1); SELECT SCOPE_IDENTITY() AS PONumber; END
At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.
The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.
See results below:
You can see that the number jumped from 8 to 1002!
- Why is this happening?
- How do I ensure that numbers aren't skipped like that?
- All I need is for SQL to generate numbers that are:
- a) Guaranteed unique.
- b) increment by the desired amount.
I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does? Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.