0

It was sudden trouble for me that MSSQL Server's auto-increment identity column doesn't work as I expected.

It doesn't guarantee a consistent sequence. I mean if it goes like 1, 2, 3, 4, 5 in case you restart your server it can easily continue like 10006, 10007, 10008, 10009, 10010. If you have some documents generated with these numbers (let say contracts) it can lead to the situation when your company had contracts with numbers 1, 2, 3, 4, 5 and then accidentally changed to 10006, 10007, 10008, 10009, 10010.

On other RDBMs the auto-increment column always keeps the consistency and it doesn't matter have you restarted it or not.

So now I want to change that column to be serviced by a sequence. The question is how can I do it maximum smoothly? I use Entity Framework and its migration mechanism.

The column is defined as:

CREATE TABLE [dbo].[
    -- omitted for the sake of brevity
    [CompanyNo] [bigint] IDENTITY(1,1) NOT NULL
    -- omitted for the sake of brevity
);

My best guess is to create a migration:

  1. Getting the current latest value of the column CompanyNo
  2. Modifying the column to make it no auto-increment
  3. Creating the sequence with start value of what it was on step 1

Technologies:

  • Entity Framework 6 + Migrations + Code First
  • MSSQL
  • ASP.NET MVC
kseen
  • 359
  • 8
  • 56
  • 104
  • Keep in mind that sequences are not guaranteed to be gapless either, unless you create them with `NO CACHE`, which forces SQL Server to use a transaction to commit every increment -- exactly what was changed for identities to increase performance. Strongly consider changing the rest of your process so it doesn't need to rely on a perfectly gapless sequence, but is content with uniqueness -- performance aside, this greatly increases robustness (and note that contiguous sequences can inadvertently expose sensitive information, such as how many contracts you close per day). – Jeroen Mostert Oct 29 '19 at 11:39
  • Note that step 2 in your process "modify the column to make it no auto-increment", isn't possible in SQL Server -- the `IDENTITY` property of a column cannot be changed after the table has been created. If you don't want to recreate the database, a new table will have to be created and the old data transferred. This can be done without trouble with [`ALTER TABLE SWITCH`](https://stackoverflow.com/a/26048363/4137916), but I doubt EF can do something like that automatically; you'd need a manual migration step. – Jeroen Mostert Oct 29 '19 at 11:47

1 Answers1

1

My suggestion is to add a timestamp column to your table which logically records when a given record were added. Then, when you want to generate your sequence, you may do it on the fly using ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER (ORDER BY ts_col) seq
FROM yourTable;

You should not be relying on an auto increment column if you need a guaranteed ordered sequence, as it was not intended for that. The contract of an auto increment column says that the generated values will always be unique and generally increasing, but not necessarily in a continuous sequence.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This is one way of solving one aspect of the problem, but it's worth noting that this only keeps consistent numbers if no rows are ever deleted (or retroactively inserted). If there are, rows will suddenly shift their numbering, which is probably even less desirable than avoiding gaps for new rows. – Jeroen Mostert Oct 29 '19 at 11:48