在SQL Server 2014中发生数据库错误时，自动增量值正在更改

Question

Contract_id is the primary key and its autoincrement value

I am using sql server 2014 and codeigniter when adding data to database , i am getting error while inserting data to database.The problem was a date format issue.I have fixed the issue and insert again to database successfully.

But the problem is Autoincrement (primary key) value is skiping and also Next value of sequence also changing.How do i stop skiping autoincrement value when ever am getting database error in front end?

public function save_contract() { 

        date_default_timezone_set('Asia/Dubai');
        //$created_Dt        = '2019-07-03';
        $created_Dt        = date('Y-m-d H:i:s');
        $this->db->select('NEXT VALUE FOR contacts_seq as contractid');
        $query = $this->db->get();

        foreach ($query->result_array() as $row)
        {
           $contract_number = $row['contractid'];
           //echo $contract_number;
        }



    $data = array(
           'Contract_no' => $this->input->post('getcontract_no'). $contract_number,
            'status' => "active",
            'created_Dt' => $created_Dt
        );

 $insert_id = 0;
     if($this->db->insert("contract", $data)){
        $insert_id = $this->db->insert_id();
        }
       return $contract_number;

    }

created sequence

CREATE SEQUENCE  contacts_seq AS INT
 START WITH 1
 INCREMENT BY 1

Answer 1

The IDENTITY property does not reuse values. From IDENTITY (Transact-SQ) - Remarks (emphasis added by me):

The identity property on a column does not guarantee the following:

• Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

• Consecutive values within a transaction - A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

• 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.

• Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

These restrictions are part of the design in order to improve performance, and because they are acceptable in many common situations. If you cannot use identity values because of these restrictions, create a separate table holding a current value and manage access to the table and number assignment with your application.

This means that if you INSERT a row and the transaction is rollbacked, for whatever reason, that seed will not be reused. The same is true for if you a delete a row; the existing rows won't be reseeded and then the "new" next value used; there will be a gap in your numbers.

The value of the IDENTITY is meant to purely be used from a relational point of view, not to create consecutive values in your table that will be maintained. If you want consecutive values at run time you should use ROW_NUMBER. If you want to store them, you'll be better off looking at a SEQUENCE