[NOTE: For fun and giggles I updated this "tool", creating a "story" home for it, which you can find here: yaflaGUID - Create Sequential GUIDs in SQL Server]

As discussed in the entry on using GUIDs in your database, GUIDs in SQL Server 2000 are, at least from the user's perspective, "random". This can lead to a fragmentation and splits in your data, and it's a common reason to avoid GUIDs in the first place.

Of course, like most problems, there are a number of possible solutions. SQL Server 2005 offers a solution in the form of NEWSEQUENTIALID() (though it's limited to being the default on a table, among other limitations).

Coincidentally I happened to be mucking around in the disassembly of rpcrt4.dll today, trying to once and for all nail down the current algorithm used for UUIDCreate (which is used behind the scenes for CoCreateGuid, which itself is used by NEWID() and System.Guid.NewGuid()), when I noticed UUIDCreateSequential in the exports. I'd never noticed this function before, and the docs verified that indeed it does create GUIDs the old sk00l way, starting with the unique MAC+time foundation, and then sequentially incrementing on each generation.

"This is like 3-lines and a minute or two to create an extended stored procedure!" think I, even though I infrequently use or advocate the use of GUIDs. Before I did that, though, I thought I'd look around to see what exists, and sure enough someone solved this problem before.

Nonetheless, for such a trivial component, especially for something that can adversely affect the stability of SQL Server, I'm prone to not trusting binaries from micro-outfits on random pages on the web. I looked for the source, and for whatever reason the source to XPGUID isn't released. I cannot overstate how ridiculously trivial this is (even adding some padding functions to make it seem more substantial). In essence it is two credible lines of code over and above the VS.NET 2003 Wizard created extended stored procedure project.

As such, I've made this available for download, source-code and all, at http://www.yafla.com/downloads/yaflaSQLGUID.zip. In it you'll find the source and a compiled Release binary, yaflaGUID.dll. You can place this (or a new build that you made yourself) in your SQL Server \binn directory and run the following command

EXEC sp_addextendedproc 'xp_yaflaGUID2005', 'yaflaGUID.DLL'

(of course you can remove it with sp_dropextendedproc)

If you want, wrap it in a User-Defined Function for some inline scalar goodness.

CREATE FUNCTION dbo.SNEWID()
RETURNS uniqueidentifier AS 
BEGIN
  DECLARE @uuid uniqueidentifier
  EXEC master..xp_yaflaGUID2005 @uuid OUTPUT
  RETURN (@uuid)
END

Voila, the old style of quasi-sequential GUIDs, with far fewer page splits (the value still does jump around, but for any closely time-related sequence of GUIDs it is sequential). Theoretically the generation of the GUID should, on average, be faster given that many are just sequentially created, however the extra indirection of the XP makes it slightly slower from a pure execution time perspective than NEWID(), but you should easily make that up in the DML calls.

I try to avoid acting as a link-propagator, but I think Nicholas has penned a very intriguing entry. Definitely worth a read. Note that he didn't say immoral, but rather he said amoral.

Skip the existential stuff at the beginning if it doesn't interest you and jump right down to The Cult of the Amateur.

For those who don't remember this long-defunct product, or never had a chance to experience it, PointCast was a multi-media push-technology news feed that took off back in 1996. Allowing the user to select a variety of channels from a number of sources (albeit all aggregated through PointCast central), along with stock tickers and customized weather, PointCast took the stage whenever the screensaver kicked in. It turned idle PCs across the land into customized news terminals, earning revenue for its corporate masters by displaying time-spliced advertisements amongst the news.

PointCast's rich graphics, generous content, and clean aesthetics made it a winner. Corporations were clamouring for PointCast caching servers to offset the 1000s of workstations all polling for updates and overwhelming their networks. Its success led many to proclaim that push technology was where it was at. Microsoft and Netscape immediately engaged in a war of push (both integrating their own technologies - Microsoft created CDF, with Active Desktop as its canvas, while Netscape created a conceptual relative of RSS...called RSS. Both stagnated when the push ferver died down, though of course the modern RSS rose from the ashes several years later).

At the height of it all, in early 1997, PointCast was offered a staggering $450 million dollar buyout. Feeling that they could do better, they held out. Not long after they were sold for a less than $10 million. This was a mini-.COM bubble popping, and should have served as a foreboding warning of the technology market implosion of the early 00s. Imagine how regretful the group who decided against the $450 million offer must have felt (and probably still feel).

I still look back fondly to PointCast. It, along with You Don't Know Jack - The Net Show, seemed to promise such a remarkable new internet world of rich content. And they managed to pull it off when most of us were lucky to have 2 KB/s connection (I now get 600KB/s).

It is amazing how much we have technically achieved, with both PCs and connections 100s of times faster, yet rich content has in many ways wallowed.

* - PointCast wasn't really push. Indeed, neither is client-side RSS. Instead they're both polled/scheduled pulls. Contrast this with SMTP, which actually is push: When ServerA has something for ServerB, it actively connects to and "pushes" the message. Pedantic point for sure, but I thought it worth making.