Tuesday, May 17, 2011

Creating Sequential GUID’s… No, not via SQL Server, but the Windows API

Alkampfer's Place - Sequential GUID are Unique

“This post refers to the previous post, where liviu warns against the “non uniqueness” of T-SQL sequential guid. The technique used is based on the UuidCreateSequential API function of the operating system. This function generates unique guid unless there is no NetworkCard in the system, but this function warns you when the guid can be considered unique only on local machine, when it returns the value RPC_S_UUID_LOCAL_ONLY.


MSDN Library - UuidCreateSequential Function

“The UuidCreateSequential function creates a new UUID.

For security reasons, UuidCreate was modified so that it no longer uses a machine's MAC address to generate UUIDs. UuidCreateSequential was introduced to allow creation of UUIDs using the MAC address of a machine's Ethernet card.

The UuidCreateSequential function returns RPC_S_UUID_LOCAL_ONLY when the originating computer does not have an ethernet/token ring (IEEE 802.x) address. In this case, the generated UUID is a valid identifier, and is guaranteed to be unique among all UUIDs generated on the computer. However, the possibility exists that another computer without an ethernet/token ring address generated the identical UUID. Therefore you should never use this UUID to identify an object that is not strictly local to your computer. Computers with ethernet/token ring addresses generate UUIDs that are guaranteed to be globally unique.

Note The UuidCreateSequential function tends to be slightly faster than the UuidCreate function. When the performance of the generation of a UUID is a significant consideration, the UuidCreateSequential function may be used.


PInvoke.net - UuidCreateSequential (rpcrt4)


sigh… I’ve only been meaning to switch my local GUID generation to this since 2006. What I get for not logging it as an official work item.   :/


Related Past Post XRef:
Create Your Own Sequential GUIDs (like NewSequentialID() in SQL 2005)


Stephen Cleary said...

If you do change your GUID strategy, please take note of my post here: A Few Words on GUIDs.

In particular, the T-SQL newsequentialid() does create a sequential GUID, but then it shuffles some of the bytes. This makes newsequentialid() GUIDs non-standard, with a higher likelihood of collision with true sequential GUIDs.

The SQL Server team did this on purpose because sequential GUIDs don't reduce index clustering much (because of the weird way SQL Server compares GUIDs). Details are in the links from my blog post.

Summary: Either use sequential GUIDs (and live with the better-than-random-but-still-not-great index clustering), or use newsequentialid everywhere (and never allow random or sequential GUIDs), or use your own combed GUID (and never allow any other type of GUID).

Greg said...

Thank you very much for that Stephen, great comment.

I had to laugh. I went to subscribe to you blog and then found I already was.... :)