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,
 CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED 
 (
    [PONumberPK] 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

ALTER TABLE [dbo].[PONumbers] ADD  DEFAULT (getdate()) FOR [DateInserted]
GO

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.

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.

UPDATE: I found out that this question has been asked before, here and here. Sorry about that. Feel free to close this one mods.

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,
 CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED 
(
    [PONumberPK] 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

ALTER TABLE [dbo].[PONumbers] ADD  DEFAULT (getdate()) FOR [DateInserted]
GO

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 and 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.

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,
 CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED 
(
    [PONumberPK] 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

ALTER TABLE [dbo].[PONumbers] ADD  DEFAULT (getdate()) FOR [DateInserted]
GO

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.