Inserts +1 to current value


I have a table consisting of columns ID (primary key) , fslno , date , amount , status.

the fslno every time get inserts as incrementing by one (+1) to the previous row value.
eg if the prev row has fslno 1005 , then fslno should be 1006 in the next row.

How to write query for that?

you can use the identity property on that particular column

IDENTITY property does that, but people often want "no gaps", and that is an issue.

What happens if:

Person A creates a new record
Person B does too

SQL allocated ID=1000 to Person A and 1001 to Person B

Then something goes wrong with Person A's insert and that transaction is rolled back. Person B's record 1001 saved OK.

For the next person that needs an ID SQL will assign them 1002, thus ID=1000 is "Missing".

You can assign an ID yourself, e.g. using

SELECT MAX(fslno)+1
FROM MyTable

but the problem with this is that it becomes a bottleneck for record insertions and, in a busy table, will BLOCK other transactions, hence better to use IDENTITY and live with the "gaps"

What is the difference between your ID (Primary key) column and your [fslno] column?

You can use the IDENTITY property as ahmeds08 suggested. However, you have to be mindful of couple of things:

  1. If you are on SQL 2012 or later, there can be gaps in the numbers unless you have set trace flag 272. See here for details.
  2. If you delete the last inserted row and then insert a new row, the identity value will not reuse the value from the deleted row, thus resulting in a gap.
  3. If you do an insert in a transaction and then rollback the transaction, the identity numbers used in the transaction that was rolled back will not be reused, thus resulting in a gap.

If you are on SQL 2012 or later, you could use the SEQUENCE object.

Then there is the old fashioned use the MAX+1 method as well.

Is that likely to have more gaps? I'm thinking:

if using IDENTITY then the ID is only assigned at INSERT and thus a gap arises only if there is a subsequent rollback.

SEQUENCE can be assigned earlier in the process, thus more chance of some Validation Logic deciding not to create the row(s), let alone a ROLLBACK occurring?

It is quite possible that SEQUENCE objects will end up leaving gaps as well. But with sequence objects, because they are a separate object, you perhaps have more control over it programmatically. That of course, does not necessarily mean that you can control it always.

1 Like

From where I am sitting a SEQUENCE object has a lot more going for it than a one-row-table with a single-column called [NextID] and a GetNextID Stored Procedure :smiley: