identity increment is jumping in sql server localdb

Question

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.

Answer 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:

• Sequences: http://msdn.microsoft.com/en-us/library/ff878091.aspx
• Next Value For: http://msdn.microsoft.com/en-us/library/ff878370.aspx