Issue with SQL identity field

Question

User-1378722245 posted

Hi All,

I have used identity field with primary key  for invoice number. This identity field number generation is not in sequence at some situation. for example : if the invoice number is 1001 then it generates next number as 1003 at some situation.

Based on the invoice number our accounting software works,

shall i reset identity column before every insert using DBCC CHECKIDENT ?

Or is there anyway to change primary key column name and refer it in child tables

Or is there any other better way to deal this 

Thanks 

Ram

Answer 1

User475983607 posted

The Identity property does not guarantee consecutive values.  

If you require consecutive values then you'll need to write T-SQL code that will provide this functionality.  How you go about implementing this this is up to you but the code is rather simple.

This snippet finds the max number and adds one.

SELECT MAX(columnName) + 1 AS nextNumber

FROM theTable

Some options are removing the Identity property from the primary key field and use custom T-SQL to create a the sequence to.  Add a new column to the table and use custom T-SQL to create a the sequence.

Answer 2

User429228380 posted

Hi Friend,

You might be some situation increase identity value increase by 2. check your identity field in your query.

Answer 3

User283571144 posted

Hi Ramganeshv,

I have used identity field with primary key  for invoice number. This identity field number generation is not in sequence at some situation. for example : if the invoice number is 1001 then it generates next number as 1003 at some situation.

As far as I know, IDENTITY (Property)'s each new value is generated based on the current seed & increment.

For example, if you add primary Key Id is '1000' and you delete it.

Then you add a new row, the primary Key Id will be '1001' not '1000'.

I guess if it is possible you delete the '1002', then you add a new line, the line's primary Key Id will be'1003'.

Or is there any other better way to deal this 

In most instances, we don't physically delete a line, we just do logical delete a line.

For example, we will add a column into the table named "Delete".

If we want to delete a line, we just set this column's value to "true".

Best Regards,

Brando