Next number of IDENT_CURRENT

Question

I want to get the next number of IDENT_CURRENT I've used

select IDENT_CURRENT ('Header') + 1

But this one gave me value = '2' when the table is empty And when I use

select IDENT_CURRENT ('Header')

It will give value = '1' in two cases, First one when the table is empty and the second one is when the table has one record. Is there any solution for this case or I have to use another method? Header Table

The table:

create table Header (
    VhrNum int primary key identity (1,1),
    Date varchar(50),
    TotVhr varchar (50),
    TotQTY Varchar(50)
)

Add the table creation script or any other way that will enable to see the structure of the table. — FDavidov, Nov 27 '16 at 06:55
No no no.... Edit your question and add this in a readable form. — FDavidov, Nov 27 '16 at 07:01
What are you trying to do? Keep in mind that if two users run this at the same time they'll get the same number then they'll get a PK error when they try to insert. Consider using a sequence instead, or using an `insert` to capture the number — Nick.Mc, Nov 27 '16 at 07:45
you are alright i didn't notice that i will learn sequence and try it Thanks — Qais khaled, Nov 27 '16 at 07:48

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

It is possible that a transaction that inserts rows in the Header table is rolled back due to errors or constraint violations and such. The transaction would assign a certain range of IDs and upon rolling back this range would not be used (i.e. the rows would not be committed and the IDs not used). That would leave a gap in the VhrNum column. So it's impossible to know what the next identity ID would be.

There are several things about identity columns that you should know of and that are spelled out clearly in the documentation of the IDENTITY property. I'll quote the relevant bullets for reference:

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

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows [...]

Consecutive values after server restart or other failures [...]

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.

For identity columns you should never rely on what the next value would be. There's no way to know what that value will be.

Thanks i got it now but what you suggest to use instead of identity to make an auto increment column , is there any solution other than sequence because i found it too complex , Thank you again — Qais khaled, Nov 27 '16 at 08:12
@QaisAl-Rawahneh That depends on your requirements. Do you require a sequence without gaps? — TT., Nov 27 '16 at 08:25
@QaisAl-Rawahneh The you will need a **counter table**. I will refer you to [this answer](http://stackoverflow.com/a/35418227/243373) I gave on the same question. Look for the second part of that answer clarifying on how to implement a counter table. — TT., Nov 27 '16 at 10:16

Next number of IDENT_CURRENT

1 Answers1