At the risk of turning this into a SQL Server blog I have to bring up one more issue that I came across. I was looking at the Microsoft SQL Server functions in PHP trying to figure out how how to get the last inserted id from an identity field. With MySQL you can use mysql_insert_id() in PHP.
A little more time on Google turned up the SELECT @@IDENTITY method. Then I came across this “Alternatives to @@IDENTITY in SQL Server 2000” page. This revealed that there are three different ways to get the value of an identity field back, each one provides a potentially different answer depending on the conditions:
SELECT @@IDENTITY
SELECT IDENT_CURRENT('tablename')
SELECT SCOPE_IDENTITY()
If you want the details on how each one works go back and read the article. The one that I’m interested in is SELECT SCOPE_IDENTITY(), it will return the identity value scoped by the connection and statement. This avoids the possible scope changes (think triggers and multiple tables).
So far MySQL hasn’t had to deal with this level of complexity for auto_increment allowing mysql_insert_id() to ignore the scope issues that SQL Server 2000 has. This is already changing though with the introduction of triggers in MySQL 5.0. It isn’t clear any more what mysql_insert_id() is going to do under these conditions. Perhaps they’ll add another function or an option to mysql_insert_id() to make it explicit.
All of this research into identity and auto_increment fields got me to wondering why they don’t just implement sequences like PostgreSQL and Oracle have. Instead of getting the id value after inserting data, you get it before hand by requesting the next value from the sequence. In Oracle you get a new value via something like this:
SELECT sequence_name.nextval FROM dual
PostgreSQL provides a similar method:
SELECT nextval('sequence.name')
To go the extra mile you can use these methods as the default value for your id field which makes it work similar to auto_increment/identity. In PostgreSQL there is a short cut for doing this via the serial data type.
Just in case this wasn’t enough to sell you on the idea of sequences there is more. You can the start value, minimum value, maximum value and increment values. In PostgreSQL you can also indicate some rules on what to do about cycling values. In Oracle the default maximum value for a sequence is 999999999999999999999999999, or if you prefer
nine hundred ninety-nine septillion.
nine hundred ninety-nine sextillion.
nine hundred ninety-nine quintillion.
nine hundred ninety-nine quadrillion.
nine hundred ninety-nine trillion.
nine hundred ninety-nine billion.
nine hundred ninety-nine million.
nine hundred ninety-nine thousand.
nine hundred ninety-nine.
The default maximum value for a sequence is slightly smaller at 9223372036854775807, or
nine quintillion.
two hundred twenty-three quadrillion.
three hundred seventy-two trillion.
thirty-six billion.
eight hundred fifty-four million.
seven hundred seventy-five thousand.
eight hundred seven.
Not bad. Both of those are very large numbers and if you are incrementing by one (which is the default) then you could go for quite awhile before you hit the wall.
So the title of this entry is sequence vs. auto_increment/identity and hopefully you’ll agree that while auto_increment and identities are simpler, their lack of features and problems with scope make them inferior to sequences. With sequences you get more features and less confusion about what they are doing (remember triggers?).
What I’d like to see is MySQL and SQL Server convert over to sequences, but continue to expose the post insert values using the same methods and functions that they do now for auto_increment/identity. They could even use a similar shortcut to PostgreSQL’s serial data type to allow for backwards compatibility when creating tables. This approach would give them the greater functionality of sequences, make them more compatible with PostgreSQL and Oracle (which would making porting apps easier) and retain backwards compatibility with their previous versions.
Any bets on how long it will take for this to happen? Is there a proposed feature list for MySQL 6.0 and SQL Server 2010 🙂