Home / Questions / 416728
Accepted
Lorelei Symphony
Asked: 2021-12-25 13:28:27 +0800 CST 2021-12-25 13:28:27 +0800 CST

Why does SqlServer save me an iD int Identity as record number 100-something, when the previous one is 7 ? - C#

  • 772

I have a Products table in a database, with the following fields:

Id int Identity Primary Key

Int Code Not Null

Name varchar(100) Not Null

Price float Not Null

Quantity int Not Null

Fk_Codigo int Foreign Key References Invoice(Codigo);

"manually"(Microsoft SQL Server Management Studio) I inserted 7 records:

INSERT INTO Product(Code, Name, Price, Quantity, Fk_Code) VALUES(10,'Televisor',9000,2,1);

INSERT INTO Product(Code, Name, Price, Quantity, Fk_Code) VALUES(11,'Laptop DELL',15000,3,1);

INSERT INTO Product(Code, Name, Price, Quantity, Fk_Code) VALUES(12,'Refrigerator',20000,4,1); ... etc.

and the iD identity correctly placed the values ​​from 1 to 7 but in my application with Windows Forms, when I insert new products, the iD identity starts enumerating from the number 1,000.

This is how I make the connection:

  public void ProductInsert(int ProductCode, string ProductName, float ProductPrice, int ProductCant, int ProductFk)
  {
     SqlCommand command;
     string query = "INSERT INTO Producto(Codigo, Nombre, Precio, Cantidad, Fk_Codigo) VALUES(" + ProductCode + ", '" + ProductName + "', " + ProductPrice + ", " + ProductCant + ", " + ProductFk + ")";

      try
      {
         connection.Open();
         command = new SqlCommand(query, connection);

         command.ExecuteNonQuery();
      }
      catch(SqlException e)
      {
         MessageBox.Show(e.Message);
      }
      finally
      {
        connection.Close();
      }
  }

It inserts everything fine, except the iD, it should insert 8 but instead it does so starting at 1,000.

enter image description here

c#

2 Answers

  1. Best Answer

    Re-doing the database is tempting, and it's fine for practice projects, but what would you do if that happens on the production server? Would you stop all operations to do a drop database and restore a backup just for a jump of a thousand numbers? How recent is the backup? is it corrupt?

    Our brain sees the jump and thinks that something is wrong, but in reality, for database engines it is totally indistinct .

    Why do I say it doesn't matter?

    Because your primary key doesn't necessarily have to be an auto-incrementing id. A primary key is one that uniquely and unequivocally identifies a record. In a real inventory system, a fan is not identified by its identity field. The fan is identified by its SKU or Part Number. THAT is the primary key of the table. The fan doesn't care if the previous record has identity 19 and the fan's is 134,934. Your wheres and joins will go in the sku 95% of the time.

    To answer your question "Why does it happen"?

    Identity Cache.

    The value of the identity column is stored in a system table called sys.identity_columns. This value is transaction agnostic and doesn't care if the transaction is committed or rolled back, because it doesn't roll back the value in the system table. Now, there are a multitude of reasons why the value may increase, including transactions that are rolled back, however, we are only interested in the following:

    Each type of data in sql server has a specific cache, in the case of intit is 1000 and for bigintit is 10000

    For performance reasons, sql server generates 1000 integers and caches them for the table where the insert is being performed. For some reason, your local server (I guess) crashes causing sql server to lose those thousand identities, and re-calculate them based on the value already in the system table I mentioned before. That's why you see a jump of 1000.

    How is it solved?

    If you use sql server 2017, the statement

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

    It tells the engine not to cache the identities, to avoid this case. The disadvantage is that the database engine will have to fetch the value of the identity column from the system table, rather than cache it.

    In what scenarios is it useful?

    If you are creating an invoicing system, for auditing reasons, the invoices, even when cancelled, must be consecutive YES OR YES . In these cases, you can disable the cache to avoid those jumps.

    If you're using a version prior to sql server2017, I suggest not relying so much on the identity column.

    These links will give you more information about this:

    https://www.sqlshack.com/learn-to-avoid-an-identity-jump-issue-identity_cache-with-the-help-of-trace-command-t272/

    https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

    Cheers

    • 5

  2. Identity in no version guarantees that the numbers are consecutive regardless of whether you have No-Cache activated.

    As you have been told in other answers and can be read in many places, performing an alter database can activate or deactivate the cache, something that also existed in previous versions but it was server configuration through flag 272, for which it was necessary restart the engine. It will reduce jumps, but you will lose performance.

    Test performed on a SQL Server 2019

    CREATE TABLE dbo.SALTOS 
(   
    ID INT IDENTITY(1,1) PRIMARY KEY,
    VALOR VARCHAR(1)
);
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
INSERT INTO dbo.SALTOS (VALOR)
VALUES('A')
GO
BEGIN TRAN
    INSERT INTO dbo.SALTOS (VALOR)
    VALUES ('B');

ROLLBACK TRAN;
GO
INSERT INTO dbo.SALTOS (VALOR)
VALUES('B')
GO
SELECT * FROM dbo.SALTOS;

    Result

    Identity truths and lies

    There are several ways to create a non-breaking Incremental id, but they all require more than just setting a property.

    Incremental Id. How?

    • 5