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:

"http://www.yafla.com/dforbes/stories/2005/10/17/yaflaguid.html">
yaflaGUID - Create Sequential GUIDs in SQL
Server
]

As discussed in the entry on "http://www.yafla.com/dforbes/categories/sql/2005/09/30.html#a86">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.

"http://www.flickr.com/photos/dforbes/53636436/"> alt="leaves2" hspace="8" src=
"http://static.flickr.com/25/53636436_c3b9041dd4_m.jpg" width="240"
align="right" vspace="8" />

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