Sequential GUIDs in SQL Server

[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 SQLServer]

As discussed in the entry on usingGUIDs in your database, GUIDs in SQL Server 2000 are, at leastfrom the user’s perspective, “random”. This can lead to afragmentation and splits in your data, and it’s a common reason toavoid GUIDs in the first place.

leaves2

Of course, like most problems, there are a number of possiblesolutions. SQL Server 2005 offers a solution in the form ofNEWSEQUENTIALID() (though it’s limited to beingthe default on a table, among other limitations).

Coincidentally I happened to be mucking around in thedisassembly of rpcrt4.dll today, trying to once and forall nail down the current algorithm used forUUIDCreate (which is used behind the scenes forCoCreateGuid, which itself is used byNEWID() andSystem.Guid.NewGuid()), when I noticedUUIDCreateSequential in the exports. I’d nevernoticed this function before, and the docs verified that indeed itdoes create GUIDs the old sk00l way, starting with the uniqueMAC+time foundation, and then sequentially incrementing on eachgeneration.

This is like 3-lines and a minute or two to create anextended stored procedure!” think I, even though Iinfrequently use or advocate the use of GUIDs. Before I did that,though, I thought I’d look around to see what exists, and sureenough someone solvedthis problem before.

Nonetheless, for such a trivial component, especially forsomething that can adversely affect the stability of SQLServer, I’m prone to not trusting binaries from micro-outfitson random pages on the web. I looked for the source, and forwhatever reason the source to XPGUID isn’t released. I cannotoverstate how ridiculously trivial this is (even adding somepadding functions to make it seem more substantial). In essence itis two credible lines of code over and above the VS.NET 2003 Wizardcreated extended stored procedure project.

As such, I’ve made this available for download, source-code andall, 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 buildthat 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 withsp_dropextendedproc)

If you want, wrap it in a User-Defined Function for some inlinescalar 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 pagesplits (the value still does jump around, but forany closely time-related sequence of GUIDs it is sequential).Theoretically the generation of the GUID should, on average, befaster given that many are just sequentially created, however theextra indirection of the XP makes it slightly slower from a pureexecution time perspective than NEWID(), but you should easily makethat up in the DML calls.