CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Alan2
Alan2

Reputation: 24572

An indentity column keep auto-incrementing by 1000 instead of 1 in SQL server?

I have a table in SQL Server 2012:

CREATE TABLE [dbo].[T] (
    [TId] INT IDENTITY (1, 1) NOT NULL,
    ...
    [ModifiedDate] DATETIME NOT NULL
);

I understand the idea of the identity column is to increment by one but I notice that for this particular table it always seems like it increments by 1000. Can someone tell me why this is? I have other tables and as far as I can see those others just increment by 1.

Upvotes: 1

Views: 1488

Answers (1)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36651

It's a SQL Server bug reported earlier

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Description

When a table with less than 1000 rows that has an identity value is part of a database that is failed over in an AlwaysOn availability group, the identity is reseeded to 1000. If the identity value is already over 1000, no reseed occurs.

This also occurs if you restart the server.

Upvotes: 2

Related Questions