MS SQL: IDENTITY increments more than defined...

Jacob Storgaard Jensen

Posted on 26/06/2015 14:31:35

Hi,

I've created a table via the SQL Firehose:

CREATE TABLE NewTableTest
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name varchar(255) NOT NULL
)

Then I run this a few times to add records to the table:

INSERT INTO NewTableTest (Name)
VALUES ('MyName')

The first record will be ID=2, the next will be ID=4 etc. It jumps a number every time...

What the hell am I doing wrong?

Replies

Nicolai Høeg Pedersen

Posted on 26/06/2015 16:16:02

This post has been marked as an answer

Dunno! Sounds oddd though. Maybe SQL Firehos runs the SQL twice, once in a transaction that is rolled back...

Votes for this answer: 1

Jacob Storgaard Jensen

Posted on 26/06/2015 16:46:37

You are right, just tried creating the rows from a razor-template, which created the correct numbers... Weird :-)

But in my search for the answer, before you replied, I found an article, saying that if the SQL-server is restarted, it will jump 1000 before it continues... https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity

Here they mention that to avoid this the SQL-server needs a tartup parameter " -t272"... Do you know if the Free and Express solutions are running on a SQL server with this parameter?

I need the ID column for generating member numbers which I would not like to skip 1000 if the SQL-server is restarted...

Nicolai Høeg Pedersen

Posted on 26/06/2015 16:58:24

Hi Jacob

I've never seen unmbers jump because of a SQL server restart.Your post is regarding a specific configuration of SQL Server

If you rely on the numbers to be sequential, you should use a colmn for that purpose and find the new value your self.

BR Nicolai

You must be logged in to post in the forum

Developer forum

MS SQL: IDENTITY increments more than defined...

Replies