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.