1

Ok so I used this code to make the table:

CREATE TABLE Clients
(
ID int IDENTITY(1,1) PRIMARY KEY,
NAME varchar(20) NOT NULL,
BALANCE int NOT NULL,
)

And it worked good for the first few times and after when i add a new record it gives it like an random id:

The problem

I dont really know what the problem is so you might tell me?

Andrew V
  • 522
  • 10
  • 24

1 Answers1

3

This is all perfectly normal. Microsoft added sequences in SQL Server 2012

By default when you create a SEQUENCE you can either supply CACHE size. Caching is used to increase performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

To fix this issue, you need to make sure, you add a NO CACHE option in sequence creation / properties like this.

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

Sequence number

  1. use trace flag 272 - this will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
  2. use a sequence generator with the NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx)

Identity column value suddenly jumps to 1001 in sql server

Community
  • 1
  • 1
Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
  • This is such an odd "feature" - thanks for sharing this, I had no idea this would even be considered a good idea from M$ developers – Trent Feb 22 '14 at 09:37
  • http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity The caching is the reason but AFAIK you can't `NO CACHE` `IDENTITY` values. But for what it's worth, you should expect the `IDENTITY` value to increment by its step each time but you can't rely on it (caching or not) because a statement might use up an identity value ... only to be rolled back and now the generated identity value is lost in the ether. – ta.speot.is Feb 22 '14 at 09:37