Announcement

Collapse
No announcement yet.

Why "Current Identity Value" starts counting from "1000".

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Why "Current Identity Value" starts counting from "1000".

    I use:
    - Win 7x64
    - ms Sql Server 2012
    - Navicat 12.0.17 (64-bit) - Premium.

    Sometimes the id counter starts counting from "1000".
    In other words:
    - today the counter "id" counts 1, 2, 3, ...;
    - tomorrow the id counter counts 1000, 1001, 1002, 1003, ...;

    CREATE TABLE [dbo].[tbl_03_GroupsStud] (
    [id_groupStud] int IDENTITY(1,1) NOT NULL,
    [id_group] int NULL,
    [id_stud] int NULL,
    [groupStud_descript] nvarchar(255) COLLATE Cyrillic_General_CI_AS NULL,
    CONSTRAINT [PK_tbl_03_GroupsStud] PRIMARY KEY NONCLUSTERED ([id_groupStud])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)
    ON [PRIMARY],
    CONSTRAINT [FK_id_grp] FOREIGN KEY ([id_group]) REFERENCES [dbo].[tbl_01_Groups] ([id_group]) ON DELETE NO ACTION ON UPDATE NO ACTION
    )
    ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tbl_03_GroupsStud] SET (LOCK_ESCALATION = TABLE)


  • #2
    According to the SQL Server manual, the reported issue should be a behavior in SQL Server:

    Consecutive values after server restart or other failures -SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

    For more information, you can refer to this link - https://social.msdn.microsoft.com/Fo...um=transactsql

    Comment

    Working...
    X