Think of a generator as a “thread-safe” integer counter that lives inside a Firebird database. You can create one by giving it a name:

CREATE GENERATOR GenTest;

Then you can get its current value and increase or decrease it just like a “var i:integer” in Delphi, but it is not always easy to “predictably” set it directly to a certain value and then obtain that same value – it's inside the database, but outside of transaction control.

“Sequence” is the official SQL term for what Firebird calls a generator. Because Firebird is constantly striving for better SQL compliance, the term SEQUENCE can be used as a synonym for GENERATOR in Firebird 2 and up. In fact it is recommended that you use the SEQUENCE syntax in new code.

Although the word “sequence” puts the emphasis on the series of values generated whereas “generator” seems to refer primarily to the factory that produces these values, there is no difference at all between a Firebird generator and a sequence. They are just two words for the same database object. You can create a generator and access it using the sequence syntax, and vice versa.

This is the preferred syntax for creating a generator/sequence in Firebird 2:

CREATE SEQUENCE SeqTest;

Generator declarations are stored in the RDB$GENERATORS system table. Their values however are stored in special reserved pages inside the database. You never touch those values directly; you access them by means of built-in functions and statements which will be discussed later on in this guide.

The structure of the RDB$GENERATORS system table is as follows:

And, as from Firebird 2.0:

Note that the GENERATOR_ID is – as the name says – an IDentifier for each generator, not its value. Also, don't let your applications store the ID for later use as a handle to the generator. Apart from this making no sense (the name is the handle), the ID may be changed after a backup-restore cycle. The SYSTEM_FLAG is 1 for generators used internally by the engine, and NULL or 0 for all those you created.

Now let's have a look at the RDB$GENERATORS table, here with a single self-defined generator:

Generators store and return 64-bit values in all versions of Firebird. This gives us a value range of:

So if you use a generator with starting value 0 to feed a NUMERIC(18) or BIGINT column (both types represent 64-bit integers), and you would insert 1000 rows per second, it would take around 300 million years (!) before it rolls over. As it is pretty unlikely mankind will still walk on this planet by then (and still use Firebird databases), that's nothing to be really worried about.

A word of warning though. Firebird speaks two SQL “dialects”: dialect 1 and dialect 3. New databases should always be created with dialect 3, which is more powerful in a number of respects. Dialect 1 is a compatibility dialect, to be used only for legacy databases that were first created under InterBase 5.6 or earlier.

One of the differences between the two is that dialect 1 has no native 64-bit integer type available. NUMERIC(18) columns for instance are stored internally as DOUBLE PRECISION, which is a floating point type. The biggest integer type in dialect 1 is the 32-bit INTEGER.

In dialect 1 as in dialect 3, generators are 64-bit. But if you assign the generated values to an INTEGER column in a dialect 1 database, they are truncated to the lower 32 bits, giving an effective range of:

Although the generator itself would go on from 2,147,483,647 to 2,147,483,648 and beyond, the truncated value would wrap around at this point, giving the impression of a 32-bit generator.

In the situation described above, with 1000 inserts per second, the generator-fed column would now roll over after 25 days (!!!) and that is indeed something to have an eye on. 2³¹ is a lot, but then again not that much depending on the situation.

Since Firebird version 1.0, the number of generators you can have in a single database is limited only by the maximum assignable ID in the RDB$GENERATORS system table. Being a SMALLINT, this maximum is 2¹⁵-1 or 32767. The first ID is always 1, so the total number of generators cannot exceed 32767. As discussed before, there are 8 or 9 system generators in the database, leaving room for at least 32758 of your own. This should be amply enough for any practical application. And since the number of generators you declare has no effect on performance, you can feel free to use as many generators as you like.

As said, generators live outside of transaction control. This simply means you cannot safely “rollback” generators inside a transaction. There may be other transactions executing at the same time that change the value while your transaction runs. So once you have requested a generator value, consider it as “gone forever”.

When you start a transaction and then call a generator and get a value of – let's say – 5, it will remain at that value even if you roll back the transaction (!). Don't even think of something like “OK, when I rollback, I can just do GEN_ID(mygen,-1) afterwards to set it back to 4”. This may work most of the time, but is unsafe because other concurrent transactions may have changed the value in between. For the same reason it doesn't make sense to get the current value with GEN_ID(mygen,0) and then increment the value on the client side.