What sequences are, who supports them, and why they're better than GUIDs or auto-increment columns except when they aren't.
When I say "sequence" I actually mean "sequence generator" because that's what SQL's CREATE SEQUENCE statement actually creates. So a sequence is a black box that produces an integer with a guarantee that this integer is greater than the previous integer that it produced, and less than the next integer that it will produce. Thus if I invoke it five times I might get 2, 4, 5, 6, 7. In poker that wouldn't be considered a sequence because there's a gap between 2 and 4. We can make sequences with few gaps or no gaps, but they're expensive.
Sequences are good for generating unique keys, although they're not the only tool for that.
This is the minimal scenario with a good DBMS.
An administrator says "create sequence x", with most things default: start with 1, go up by 1, cache 20.
A user says "get the next value from x".
The server grabs the numbers between 1 and 20. It writes to a log: "last grab value = 20". It saves in memory: 1 number is used, 19 remain in the cache. The user gets a number: 1.
A user says "get the next value from x". The server does not need to grab more numbers or write to a log. It saves in memory: 2 numbers are used, 18 remain in the cache. The user gets a number: 2.
This continues until there are no more numbers left in the cache -- at that point the server does another "grab" and another "log write", then proceeds with a new cache.
Ordinarily the process needs some mutexes but not DBMS locks, although some DBMS vendors like to maintain an in-memory system table that contains the highest value for user convenience. So sequence overhead might be cheap compared to the overhead of maintaining a persistent table with an auto-increment column.
There are two reasons that there will be gaps:
(1) because once a value is retrieved it's gone, so ROLLBACK won't cause the sequence generator to put a number back
(2) because a crash can happen any time after the grab, and when the server restarts it will use the last value written to the log, so up to 20 numbers can be left unused. Of course a different cache size would mean a different number of possible unused, or "burnt", numbers.
In the table below, the left column has pieces of a CREATE SEQUENCE statement, the other columns show which DBMSs support it.
CREATE ANS DB2 Fir HSQ Inf Ing Ora Pos Syb SQL [OR REPLACE] DB2 Syb [ TEMPORARY | TEMP ] Pos SEQUENCE ANS DB2 Fir HSQ Inf Ing Ora Pos Syb SQL [IF NOT EXISTS] Inf Pos sequence-name ANS DB2 Fir HSQ Inf Ing Ora Pos Syb SQL [AS data type] ANS DB2 HSQ Ing SQL [START WITH n] ANS DB2 HSQ Inf Ing Ora Pos Syb SQL [ INCREMENT BY n ] ANS DB2 HSQ Inf?Ing Ora Pos Syb SQL [ NO MINVALUE | MINVALUE n ] ANS DB2 Inf Ora Pos Syb SQL [ NO MAXVALUE | MAXVALUE n ] ANS DB2 Inf Ing Ora Pos Syb SQL [ NO CYCLE | CYCLE ] ANS DB2 Inf Ing Ora Pos Syb SQL [ NO CACHE | CACHE n ] DB2 Inf Ing Ora Pos Syb SQL [ OWNED BY column-name } ] Pos [ ORDER | NOORDER ] Ora [ KEEP | NOKEEP ] Ora [ SESSION | GLOBAL ] Ora [ SEQUENTIAL | UNORDERED] Ing
ANS = ANSI/ISO 9075-2 (the SQL:2011 standard) with optional feature T176 "external sequence generator"
DB2 = IBM DB2 for LUW
Fir = Firebird
HSQ = HSQLDB
Inf = Informix
Ing = Ingres
Ora = Oracle
Pos = PostgreSQL
Syb = Sybase IQ (not Sybase ASE)
SQL = Microsoft SQL Server
CREATE SEQUENCE x_seq AS DECIMAL(20) START WITH -1000000 CACHE 1000;
The chart doesn't show some trivial variations, for example some DBMSs allow NOMAXVALUE along with or instead of NO MAXVALUE, and in some DBMSs WITH or n can be optional.
I believe that clauses like START WITH, INCREMENT BY, MIN[IMUM]VALUE have obvious meanings. The truly significant ones are [AS data-type] and CACHE.
[ AS data-type ]: this is the only clause that's not supported by all three of the Big Three: DB2 and SQL Server have it but Oracle doesn't. Oracle just says there are up to 28 digits. INTEGER (+ or minus 2 billion) might be enough, but if you do a billion "next value" calculations in a day, you'd run out of numbers in (4294967295 / 1000000000) = 4 days.
[CACHE n]: this is not an ANSI clause because it's strictly for performance, and for an idea of how important it is I'll quote IBM developerWorks:
"The main performance issue to be aware of for identity and sequence is that, for recoverability reasons, generated values must be logged. To reduce the log overhead, the values can be pre-reserved (cached), and one log record is written each time the cache is exhausted. ... As the results of tests 41-51 in Appendix A show, not having a cache is extremely costly (almost nine times slower than the default)."
The operation for "get next value" is NEXT VALUE FOR sequence-name. Some DBMSs shorten it to NEXTVAL and some DBMSs allow for "getting current value" or "getting previous value" i.e. repeating the NEXT VALUE result. But the main things to know are:
The DBMS increments once per row, not once per time that the DBMS sees "next value". This is remarkable, because it's not how a function invocation would work. In this respect PostgreSQL is non-standard, because it will increment every time it sees "next value".
There is no race-condition danger for "getting current value". User #1 does not see the number that User #2 gets, because it's stored within the session memory not global memory.
Getting from a sequence is certainly not deterministic, and is usually disallowed for some operations that would require "next value" to come up often. Oracle disallows it with DISTINCT, ORDER BY, GROUP BY, UNION, WHERE, CHECK, DEFAULT, subqueries, and views; other DBMSs are more easygoing.
You'll want to find out what you're inserting. The simple way is to get the next value first into a variable, then insert it:
SET x = NEXT VALUE FOR x_seq;
INSERT INTO t VALUES (x, ...)
but that involves two trips to the server. If possible, one wants to do the database operation and return the next value to the user, all via one statement, as in the SQL:2011 non-core (optional) feature T495 “Combined data change and retrieval”. Unfortunately (I've decried this heresy before), you'll often see this done with UPDATE ... RETURNING.
If I just want a unique number, why don't I get the client or the middleware to generate a GUID for me? First let me state what's good.
* Zero locks and zero log writes.
* A GUID has a 122-bit number. No, not a 128-bit number because there are a few bytes for version number and for future purposes. No, we can't hope that there will be even distribution across the range so 2**122 is a dream. But (2**122) = 5.316912e+36, so the chances of generating duplicate numbers is still estimated as: not in this lifetime.
* So what if there's a duplicate? If you're generating a GUID for the sake of a surrogate key, then it will get inserted in a unique index, and so there will be a nice clean error message if the same value reappears.
Now this is what's bad.
* GUIDs are long. It's not just a difference between 128 bits and 64 bits, because serial numbers start at small numbers like '1', while GUIDs are always long strings when you print them out. Not only does this mean that end-users wouldn't want to be subjected to them, it means that they'd take more space to store. And, if they're used for primary keys of an index-organized table, they might form part of secondary keys, so the extra space is being consumed more than once.
Therefore it's not a settled matter. But people do prefer sequences for the other main reason: despite their problems, they're generally sequential.
The other way to get a sequence is to define a column as IDENTITY, or some close equivalent such as PRIMARY KEY AUTO_INCREMENT.
Clearly, SEQUENCE is better when the same generated value is useful for multiple tables, or for no tables. But what about the common situation where the generated value has something to do with a single column in a single table -- then maybe IDENTITY is better for performance reasons.
I recently encountered a good insult: "You're not stupid, you just have bad luck when you think." Here is an example of thinking. See if you can spot the bad luck.
"The DBMS is going to generate a number as a primary key. Primary keys have B-tree indexes. The NEXT VALUE will be the current largest value, plus one. That value is in the last page of the index. I have to read that page anyway, because that's where I'm going to add the key for the NEXT VALUE that I'm INSERTing. Therefore the overhead of using IDENTITY is negligible."
Did you have any trouble spotting that that is not true, because the largest value might have been DELETEd? An identity value must not be re-used! Therefore there is a bit of overhead, to store what was the maximum value for all time, separately.
(Incidental note: MySQL and MariaDB and Oracle Rdb and SQL Server 2012 unfortunately do destroy the maximum auto_increment value when I say TRUNCATE TABLE. But DBMSs like DB2 for i will by default preserve the maximum IDENTITY-column value, as the SQL standard requires.)
People have compared IDENTITY versus SEQUENCE performance. On SQL Server 2012 one tester found that identity is always better but another tester found that sequence catches up when cache size = 100,000. On Oracle 12c a tester found that sequences took 26 seconds while identities took 28 seconds. On DB2 a tester found that "Identity performs up to a few percent better." Averaging the results: it depends.
And it's not necessarily a GUID versus SEQUENCE versus AUTO_INCREMENT contest. There are lots of ways that users can make their own unique values, with timestamps and branch-identifiers and moon phases. They're not standardized, though, so I leave such ideas as exercises for readers who think they can do better.
The two things you want to avoid are hot spots and cache misses. Choose any one.
A hot spot is caused when multiple users are all trying to modify the same index pages. That's inevitable if User #1 gets value 123456, User #2 gets value 123457, and User #3 gets value 123458. Naturally those will probably fall on the same page, and so cause contention. The solution is to hash or reverse the number before writing.
A cache miss is caused when multiple users are not all reading the same index pages. If they were, then the chances would be high that the page you want would be in the cache, and you'd save a disk read. The situation can arise when everybody needs to access a group of rows that were put in around the same time, for example "rows inserted today". The solution is to not hash or reverse the number before writing.
The easiest way to reduce gaps is to say CACHE 0 or NO CACHE when you create the sequence. As we've seen, this has bad effects on performance, so do it only when you have some rule saying "no gaps" (read "Gapless sequences" for an example due to German tax laws). It's likely that you'll still have something that's faster than a separate auto-incrementing table.
Or you can try to plug the gaps after they're made. Itzak ben-Gan wrote a few tips about that, and so did Baron Schwartz.
In 2003 in MySQL Worklog Task #827 I explained: "In the Bordeaux group leaders' meeting (November 2003), "Oracle-type sequences" was specified as an item for version 5.1." You can judge how well that came out.
MariaDB's worklog task 10139 is somewhat more current -- there's an assignee and there's recent activity. I'm dubious about the proposed plan, it specifies very little and it's something involving a read-uncommitted table that contains the latest high number, and then the dread words appear "see [URL of postgresql.org documentation] for details". However, plans often change.
Unless I'm behind the times, I predict that MySQL or MariaDB would have a problem imitating Oracle's GRANT SEQUENCE (a separate privilege that only affects sequences). Making a new privilege is still hard, I believe.
When or if the syntax changes, our GUI client for MySQL and MariaDB will of course be updated to recognize it.
My old DBAzine article "Sequences and identity columns" is still mostly valid.