0

Identity column values jump to 1000 when localDB service is restarted. An annoying feature of SQL Server 2012 and 2014. This causes big gaps. There is a solution for Express edition (adding -T272 option to db instance startup parameters to revert to the 2008 behavior.) But the localDB instance doesn't show up in the SQL Server 2014 configuration manager, I cannot access the parameters. So how can we configure it? I dont want to use sequences.

Sinan Dinç
  • 1
  • 2
  • If you *care* about what numeric values are being assigned to identity columns, you're using them wrong. They should be treated as opaque binary blobs that just happen to easily be *stored* in numeric columns/variables. – Damien_The_Unbeliever Nov 12 '14 at 13:13
  • I do not care about numeric values. My identity type is int. My application is web crawler bot and can insert up to 10 million records per day depending on usage. In this scenario, identity will reach the integer limit (4294967296) in just one year. It is worse if there are gaps. – Sinan Dinç Nov 12 '14 at 13:35
  • Is it better if everything has to go slowly because you're forcing serialization to get identity columns without gaps (but, of course, smaller gaps can occur for other reasons). If you're worried about exhausting an `int`, that's a separate issue with an obvious fix - just move to `bigint`. You won't exhaust it. – Damien_The_Unbeliever Nov 13 '14 at 08:30
  • I have just answered this question here - http://stackoverflow.com/questions/39043952/how-can-i-specify-that-my-app-should-start-sql-server-localdb-with-a-trace-flag/41248215#41248215 – Richard Moore Dec 20 '16 at 17:43

1 Answers1

1

You can replace your identity columns with a sequence column.

CREATE SEQUENCE Id_Sequence AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
    NO CACHE

Then you can reconfigure your identity column as just an INT, and to get the value to insert into the INT column, you can:

INSERT MyTable (Id) VALUES (NEXT VALUE FOR Id_Sequence)

Better yet, you can just make it the default value so the behavior is the same as an identity.

ALTER TABLE MyTable ADD DEFAULT NEXT VALUE FOR Id_Sequence FOR Id

For reference:

Jason W
  • 13,026
  • 3
  • 31
  • 62
  • As I said, using sequences is not an option – Sinan Dinç Nov 12 '14 at 13:51
  • Sorry I missed that comment. Are you open to considering an identity table? I just don't believe you're going to solve the identity skipping with localdb. If you're worried about the size of the field, then use BIGINT. – Jason W Nov 12 '14 at 14:44