Warm tip: This article is reproduced from stackoverflow.com, please click
codeigniter php sql sql-server

Autoincrement value is changing when database error occured in sql server 2014

发布于 2020-03-27 10:31:43

enter image description here 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
Questioner
duo
Viewed
78
分享
Larnu 2019-07-03 23:51

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

Jean-François Fabre 2019-07-04 04:44:58

Comments are not for extended discussion; this conversation has been moved to chat.

Related issues

1
Why "LAST_VALUE(birthdate)" window function clause necessary compare to "FIRST_VALUE(birthdate)"?
2
Group by query with rollup
3
SQL Server. How to refresh the intellisense?
4
SQL query pivot, move values to top
5
Best way to implement an audit trail in SQL Server?
6
TLS 1.2 SQL Server and Delphi Client
7
Count amount of same value
8
SQL Insert does column order matter
9
SQL Server query problem. example is in excel sheet picture
10
SSIS package works in debug mode, but hangs at step 2 when launched from the SQL Server job