AutoIncrement does not yield sequential numbers

rocatis

I have a strange and somewhat worrying problem with a customer's Change Log: the Entry No. field is not sequential. This field has the (SQL) attribute AutoIncrement=Yes.

The problem has occurred twice, both times across dates. I suspect both have happened in conjunction with a server reboot.

30-09-14: Entry No. = 11192
01-10-14: Entry No. = 21191

A couple of weird things about this that are probably random, is that the the gap is 9999 and that the first and last digit are swapped.

05-01-15: Entry No. = 26388
06-01-15: Entry No. = 36367

This time the gap is 9979.

It's impossible for the gaps to have occurred because entries in the log have been deleted.

The customer (and in particular their accountant) more or less demands an explanation of this problem which I find totally reasonable. My problem is that at the moment my best reply is that it's SQL Server that is maintaining that field and that the problem must somehow be related to the (assumed) server reboots.

Can anyone come up with something better?

kine

Do not forget that uncommited transactions (ending with error) will allocate the number from the sequence, but entry will not exists because transaction was cancelled (rolled back). It means, Autoincrement does not guarantee continuous row by definition...

Of course, another "why" could be that entries are deleted, than you need to check the permissions (who has delete permissions on the table) and who has access to the SQL directly.

rocatis

kine wrote:

Do not forget that uncommited transactions (ending with error) will allocate the number from the sequence, but entry will not exists because transaction was cancelled (rolled back). It means, Autoincrement does not guarantee continuous row by definition...

Then it could very well be argued that the Change Log is a very poor table to use AutoIncrement on as the reliability of that table is rather depending on a continuous counter.

Besides, it's simply impossible that the table should have almost 10000 uncommitted transactions - twice. Bear in mind that the numbers tell us that around 5000 entries are created over a three month period.

Of course, another "why" could be that entries are deleted

I guess it's a theoretical possibility but that would entail that somebody in less that 24 hours (18, in fact) actually created 10000 entries and then deleted them. Seeing as changes done in code does not affect the Change Log, that would mean that somebody actually sat down before the screen and did 10000 changes. That comes out as about 10 changes a minute :shock:

kine

Sometimes there could be code calling the functionality.

rocatis

kine wrote:

Sometimes there could be code calling the functionality.

Changes done through code are not registered in the Change Log (unless you specifically code it).

Anyways, I found the answer: it's a "feature" in SQL Server: http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is. In other words, if you want to make sure you're dealing with an unbroken sequence of entries, do not use AutoIncrement.

kine

rocatis wrote:

kine wrote:

Sometimes there could be code calling the functionality.

Changes done through code are not registered in the Change Log (unless you specifically code it).

I know, it is why I have wrote that code could call the function...

But I am happy that you have found the reason, very interesting, I have not met it yet, but I am sure I will...

:thumbsup:

DenSter

Why would these gaps be worrisome? The entry number is just a number, it doesn't have any meaning. it's quite common for tables to have a single integer field as a primary key, which frees you up to change the data integrity rules of the table in any way way you want

Also when you insert 1000 records, and the auto increment sits at 1000, and then you delete all records, SQL Server will issue the next number at 1001. It doesn't care about keeping the sequence intact.

rocatis wrote:

In other words, if you want to make sure you're dealing with an unbroken sequence of entries, do not use AutoIncrement.

Yep