16

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:

PO Numbers

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.

23

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were actually issued. This is documented here. There is a trace flag that changes this behavior such that every IDENTITY assignment is logged*, preventing those specific gaps (but not gaps from rollbacks or deletes); however, it is important to note that this can be quite costly in terms of performance, so I'm not even going to mention the specific trace flag here.

* (Personally, I think this is a technical problem that could be solved differently, but since I don't write the engine, I can't change that.)

To be clear about how IDENTITY and SEQUENCE work:

  • Neither is guaranteed to be unique (you need to enforce that at the table level, using a primary key or unique constraint)
  • Neither is guaranteed to be gapless (any rollback or delete, for example, will produce a gap, this specific problem notwithstanding)

Uniqueness is easy to enforce. Avoiding gaps is not. You need to determine how important it is for you to avoid these gaps (in theory, you should not care about gaps at all, since IDENTITY/SEQUENCE values should be meaningless surrogate keys). If it is very important, then you should not be using either implementation, but rather roll your own serializable sequence generator (see some ideas here, here and here) - just note that it will kill concurrency.

Lots of background on this "problem":

  • This answer (except the "trace flag" part) also applies to most other SQL databases (the ones that have sequences anyway). – mustaccio Jul 7 '15 at 17:54
  • Thanks for the answer. Uniqueness is the single most important requirement. Gaps aren't a big deal, as long as they aren't large. e.g. going from 1 to 4 would be acceptable, but from 4 to 1003 would not. – Ege Ersoz Jul 7 '15 at 19:19
  • 1
    Short version: the ID values will be used as purchase order numbers. The customer runs monthly reports and wants to be able to quickly tell how many POs were submitted that month just by looking at the PO number. So we can't have it increment by ~1000 (there is a weekly maintenance where all servers, including the DB server, are restarted). – Ege Ersoz Jul 7 '15 at 20:23
  • 3
    Why don't you give them a very easy report that just uses ROW_NUMBER() OVER (PARTITION BY Month ORDER BY ID)? Again, the ID number should be meaningless, that is a terrible way to eyeball how many orders were taken. What if you have a bug in your code that deletes 1000 rows or rolls back 275 transactions, or 500 orders are legitimately cancelled? – Aaron Bertrand Jul 7 '15 at 20:31
  • 1
    @Ege: "... tell how many ... just by looking at the PO number". Your users are going to be disappointed. Identity values simply don't work that way, nor should you (or they) make any such assumption. Unique? Yes. Consecutive? No. The correct way to count PO's submitted during a month is ... to count the number of PO's raised during that month, based on some [unalterable] Date field in each record. – Phill W. Sep 11 at 14:54
-4

This is problem of SQL Server. All you can do is reseed the column.

delete the entries with wrong column id. Reseed the column identity. And then the next entry has proper ID to it.

Reseed Identity using following sql command : DBCC CHECKIDENT ('YOUR_TABLE_NAME', RESEED, 9) -- 9 is last correct Id

  • 1
    What do you mean with "delete the entries"? – ypercubeᵀᴹ Sep 11 at 13:29
  • 2
    Hmmm .. seems deleting entries might just lead to data loss. – Michael Green Sep 12 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.