CODEWITHSUNDEEP
sqlsql-serversequenceidentity
user2967049
user2967049

Reputation: 39

new(ish) Sequence in SQL Server

I have an old site that used to use Identity to get a new invoice number.

In SQL Server 2005, this worked fine, but since moving to SQL Server 2014, I get gaps (approx 1000 after a reboot).

Yes I know (now) you shouldn't use Identity for this. So I thought the solution would be using a Sequence.

I did this

CREATE SEQUENCE SeqInvoice
AS INTEGER
START WITH 2971
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999999
NO CYCLE;

And to get next number do this.

 SELECT NEXT VALUE FOR SeqInvoice;

worked fine ..... until I noticed a gap - it jumped from 3028 to 3067

I thought Sequence was supposed to not have gaps.

Can Sequence have gaps?

If so do I need to have table with last number used and use transactions to ensure no duplicates?

Upvotes: 1

Views: 98

Answers (2)

marc_s
marc_s

Reputation: 754678

A SEQUENCE in SQL Server caches a default of 50 sequential values in memory, so when a reboot or power outage etc. occurs, yes, there can be gaps of up to 50 numbers.

BUT: the CREATE SEQUENCE (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql) does allow you to specify how many cached sequential numbers you want - with the CACHE n directive. You can also use NO CACHE to have absolutely no caching - then, you should pretty much avoid gaps altogether - BUT at the price that dishing out the sequential numbers with NEXT VALUE FOR ... becomes a bit slower. Take your pick!

Upvotes: 1

Tom H
Tom H

Reputation: 47464

Think about the logic of the situation for a minute. In the case that you came up with - two users creating invoices at the same time - how do you handle it if one user gets the "next" invoice number, then they change their mind and cancel it. Or the system crashes. Or they lose their connection. These are just some of the reasons that there isn't a built in "no gaps" type of ID. SQL Server needs to be able to have ACID transactions and that can't be done with autoincrementing numbers that never have gaps.

The best solution that I can come up with is to have your system generate an invoice number as soon as someone starts to create an invoice. That's immediately saved to the database. If anything happens to that invoice (the user cancels it, the system crashes, etc.) then the invoice is canceled out in the system by giving it a canceled status code or something along those lines. That way it will still exist and you can point to it for the auditors and say, "Here's why that invoice number doesn't have a real invoice attached - the user created it, but then canceled it." (for example)

To generate the invoice number you could use a single table with a single column and single row for next_invoice_number. When your system wants to generate a new invoice number it locks that table, saves a blank invoice in the Invoices table with the next number, increments the next_invoice_number in the table, then releases the lock on the table. If you have a lot of users creating invoices then this could cause problems with users having to wait for the locks on the table to be released, but it's about the best that you can do, IMO.

Upvotes: 2

Related Questions