[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.