Subject Re: [firebird-support] Need help in Firebird
Author setysvar
>Dear All,
>I have a question. I need to store some data in Firebird database. One
of the data is a running serial number, 0 to 999999. I try the Count SQL
function, but sometimes
>after INSERT function I get same number 2-3 times. After I try to make
a tabel which contain an INTEGER field, and I update it, after I run a
QUERRY, but the situation
>is same.
>Could you give me a good sollution how can the DB make a running
serial number (not recurring)?
>After each UPDATE I run a COMMIT.
>The operating system is: Windows 7.
‚Äč>Please give me an answer ASAP.
>Thank you very much!

Hi Richard!

The reason you get the same number is most likely because of
simultaneous updates, i.e. that one transaction starts before the
previous transaction commits. COUNT have never been a reliable way of
getting a unique number. Moreover, COUNT will make things slow as the
table grows.

Rather, try:

CREATE SEQUENCE <MyPreferredSequenceName>

and then in your insert statement include

NEXT VALUE FOR <MyPreferredSequenceName>

If for some reason a transaction rolls back, that particular sequence
number is lost. Normally, this doesn't matter, but sometimes it may for
financial applications where laws say that there should be no
unexplained gaps.


PS! Your title "Need help in Firebird" is quite useless here, since
almost 50% of the emails fit that description (the other 50% are
answers). It would have been far better if it had been something like
"My numbers duplicate, I need them to be unique"