How do I create a identity column that doesn't skip 10000 every time service restarts with EF?

Question

I have a table with an identity column ID. Every time the sql service restarts and I insert a new value, the ID increases by 10000.^{[Microsoft Connect link]} (ID type is bigint)

identity increase 10000

I want a normal increment column, how can I achieve that with Entity Framework? I am using Database-first (.edmx model)

possible duplicate http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database see also https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity — phil soady, Nov 13 '13 at 14:20
If you care about the numerical values of an identity column, you're doing something wrong. As much as possible, they should be treated as opaque blobs that you just happen to be able to *store* in `int` columns/variables. — Damien_The_Unbeliever, Nov 13 '13 at 14:23

steoleary · Answer 1 · 2013-11-13T14:21:20.207

3

I assume you're using SQL Server 2012? As far as I'm aware, this behaviour is due to the way that SQL server itself preallocates and caches identity values for a column in order to improve performance, I don't think there is any way to control this through EF.

The ways around this if you require gapless sequences that I know of are:

Use a sequence object with the no cache property
Enable trace flag T272 which will disable the preallocation of identity values and revert to the SQL Server 2008 R2 type behaviour

Just to clarify, when I say gapless, I mean that if the SQL service restarts unexpectedly the sequence will always start from where it was last as every change is written to the system tables, you can still get gaps in the sequence by other means such as transactions rolling back etc.

edited Nov 13 '13 at 14:21

answered Nov 13 '13 at 13:59

steoleary

8,968
2
33
47

1

SQL Server has never guaranteed that `IDENTITY` values will create a " gapless sequence" though. – Martin Smith Nov 13 '13 at 14:04
well, yeah, but they never jumped around as much as they do now in 2012, maybe gapless was the wrong word to choose there. – steoleary Nov 13 '13 at 14:06
Ok, that much is on the link I posted. But how to use the sequence? And/or how to use the flag? I'm using Database-first hosting on Azure. – BrunoLM Nov 13 '13 at 14:20

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

0

DB:

CREATE TABLE [MyTable]
(
    [ID] [bigint] PRIMARY KEY NOT NULL,
    [Title] [nvarchar](64) NOT NULL
)

CREATE SEQUENCE MyTableID
    AS BIGINT
    START WITH 1
    INCREMENT BY 1
    NO CACHE
    ;
GO

CREATE TABLE [MyTable]
(
    [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
    [Title] [nvarchar](64) NOT NULL
);

And in the edmx set ID to computed value in the table column properties.

Thanks billinkc and guys from dba

edited Apr 13 '17 at 12:42

Community

1
1

answered Nov 13 '13 at 16:11

BrunoLM

97,872
84
296
452

2

This isn't an answer to the question you asked. The use of a `SEQUENCE` with `NOCACHE` is already mentioned in the Connect item link. Your question was about `IDENTITY` columns. They are not the same thing. e.g. `SCOPE_IDENTITY` or `@@IDENTITY` will not take any notice of sequences. – Martin Smith Nov 13 '13 at 16:40
1

Not my downvote BTW. I was just pointing out that it is not an answer to the question in the title "How do I create a identity column that doesn't skip 10000 every time service restarts with EF?" because it doesn't actually create an `identity` column. The answer to the question you asked is provided by steoleary. If there are constraints meaning you can't use it then you should have mentioned them in the question. – Martin Smith Nov 14 '13 at 00:05

How do I create a identity column that doesn't skip 10000 every time service restarts with EF?

2 Answers2