Alex King brought up a good point this week asking why don’t companies make their commercials available online? Then I saw a commercial for Visa that had a break dancing worm in it. So I first went to their website to see if it was available, which it wasn’t. My next trip was to YouTube to see if someone had posted it. Sure enough it was there. So here it is, the Visa break dancing worm commercial.

UPDATE Tue 4 Apr 2006 @ 10:10am : I guess I didn’t look very hard at the Visa website because Lauren left a comment showing how to see their commercials in just a few steps. So here is a link to Visa commercials on their site. The break dancing worm spot is called ‘Recycling’.

So I’ve finally tried out Pandora. As a trial run I’ve created a station for music that is similar to Information Society.

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

Adam Kalsey reminded me of an essay that Paul Graham posted last summer called Hiring is Obsolete (May 2005). The short version, Paul Graham was right.

Most folks have been trying to figure out what Yahoo, Google and to a lesser extent Microsoft are up to, especially with all of the purchases that have been going on lately. For instance, Yahoo bought Flickr, but from all reports Yahoo Photos is several times bigger than Flickr. Yahoo also bought Del.icio.us even though their MyWeb 2.0 was starting to offer similar services. A more recent example would be the purchase of Writely by Google. There is no doubt that Google has the money, time and people resources to build such a service themselves, but they didn’t.

Before I go on, if you haven’t read Paul Graham’s Hiring is Obsolete go do it now.

Okay, so back to the question of what the heck is going on with all of these folks being bought up by companies that have the resources do the same thing themselves. Honestly part of my gut says that this helps a lot with the open source, web head street cred crowd; but that certainly isn’t all of it. For one reason or another, larger companies don’t usually get around to developing the cool new “thing”. Paul lists some reasons for this under the product development portion of his essay: protecting existing turf, allowing their own people to experiment, the difficulty of project management for every project you could think of trying and big companies get in the way of themselves sometimes.

Yahoo and Google are buying these folks up because they get people who have demonstrated that they can come up with cool things. By bringing them into the fold they’ll get a chance to own any new ideas that they come up with and they’ll hopefully be able to make their existing products and services better as well. And by doing all this before they get really big (or go public) they can do it fairly inexpensively. Hard to believe that I just mentioned transactions in the millions of dollars as inexpensive.

So talk of a Web 2.0 bubble isn’t the same as the original bubble. I don’t expect many of these companies to reach the stage of going public, more likely that the good ones will just get purchased. Creating your own Web 2.0 app has become a resume for these talented folks. As for the future, I think Paul does a great job of predicting that:

I think the trend of big companies buying startups will only accelerate. One of the biggest remaining obstacles is pride. Most companies, at least unconsciously, feel they ought to be able to develop stuff in house, and that buying startups is to some degree an admission of failure. And so, as people generally do with admissions of failure, they put it off for as long as possible. That makes the acquisition very expensive when it finally happens.

What companies should do is go out and discover startups when they’re young, before VCs have puffed them up into something that costs hundreds of millions to acquire. Much of what VCs add, the acquirer doesn’t need anyway.

If you’ve made it this far you may be interested to know that Paul Graham has a blog now.

Welcome to a brief stop in the twilight zone of database functions.

If you want to check two (or more) fields and get the value of the first non-null field in Oracle you would use nvl. I needed to do the same thing in SQL Server 2000, which doesn’t have the function nvl (of course). After looking around SQL Server has a function that does the same exact thing called isnull.

By this point I figured I’d better be complete and find out if MySQL had a function called nvl or if they called it something else. If you guessed that it is called something else then you would be right, MySQL calls it ifnull. I was already familiar with the PostgreSQL equivalent which is called coalesce. Unlike all of the other functions mentioned above though, coalesce takes a list of fields/values/expressions instead of just two. Although the name sounds a little strange (nvl just rolls of the tongue so nicely) it certainly made more sense to have a more generalized function that would accept a list. As an additional reference the mysql compatibility project includes a ifnull function for folks porting SQL from MySQL to PostgreSQL.

So do you feel a little more enlightened now? Don’t stop now, it gets even stranger.

I got to thinking more about how much better the coalesce approach seems to be than nvl, isnull and ifnull so I did some more research to see what other databases besides PostgreSQL supported it. Guess what I found out. There is a coalesce in Oracle, a coalesce in SQL Server and a coalesce in MySQL. Huh? Why do folks even bother writing SQL that includes the use of nvl, isnull and ifnull?

That concludes our brief tour through the twilight zone of database functions for now.