NewSequentialId was interesting, but not being able to call it directly was a non-starter for us.
I found 2 implementations for NewSequentialID in an extended stored procedure, so you could use it in SQL 2000: xpguid and yaflaguid (the later includes source code, it also has an implementation of an alternate algorithm)
This is an email I originally sent to some coworkers, with some links for more information.
MSDN NewSequential ID Documentation
I came across this new SQL 2005 function - NewSequentialID() -, and I thought you might be interested.
Microsoft’s first implementation of Universally Unique IDs (GUIDs) used the network card MAC address, which is unique by itself, plus some randomized and counter data. However, there was some uproar about it infringing on privacy: it was first used in Office as internal document structure identifiers, and it was possible to determine the originating computer based on the ID (since it embedded the 6 byte MAC address). MS changed the default algorithm so you wouldn’t use the MAC address (I think it still uses it, but only as an aid, and you couldn’t reconstruct it from it). NewSequentialID()/UuidCreateSequential go back to using the MAC address for a simplified algorithm with some advantages on a DB design (ie, clustering on the primary key)
Blog entry describing the SQL developer’s logic and some notes on the implementation of NewSequentialID() by calling UuidCreateSequential. It’s interesting that they saw duplicate GUID generation on AMD 64 systems (and that they fixed it, albeit using a mutex). This is probably the reason why you can't call it directly.
A blog entry indicating that duplicates are being generated. However, his interpretation of the manual kind of makes me think that he might have some other issues. One of the posts kind of implies that there might be an issue with uniqueness on a cluster. You probably want to research it further if you want to use this function.
SQL Server Magazine article about it (if you have a subscription).
Someone that sat down to test performance of a GUID vs integers. The conclusion was that if you design properly, there is little or no difference in performance.
What no one seems to have stated is that (I think) the reason why a GUID is so close to an integer in performance is because the CPU word size has gotten bigger, and because the CPU is routinely waiting for other subsystems, and because the extra overhead involved in using data types smaller than the CPU word size more than compensates for the GUIDs large size. This is even truer now with 64 bits CPU (which is kind of why I initially justified GUIDs to myself when the first details on the Itanium where available).
More performance testing, and another alternative to random GUIDs, COMBs – using dates combined with a guid to generate an ordered GUID (same/similar thing the one above does in one of the tests).
A long thread on the good and bad of GUIDs (I found some of the links above here).
Also, it seems that the table designer is not aware of NewSequentialId() as a valid default value, and it displays some warnings when you create/modify a table using it. I don't know if SQL 2005 SP1 fixed this.