19

I'm just curious, I've always wondered why this is so.

In an attempt to find out if I could create one without the character 4 at the 15th character, I ran this...

DECLARE @GUID AS NVARCHAR(36)
DECLARE @COUNT AS INTEGER

SET @COUNT = 0
SET @GUID = CAST(NEWID() AS NVARCHAR(36))

WHILE SUBSTRING(@GUID,15,1) = '4'
BEGIN
    SET @COUNT = @COUNT + 1
    SET @GUID = CAST(NEWID() AS NVARCHAR(36))
END

PRINT 'Attempts : ' + CAST(@COUNT AS NVARCHAR(MAX))
PRINT @GUID

As you might guess, this never actually ended for me. I had this running on a server all weekend.

If NewID is supposed to always give a random ID, why is that 4 always there.

BC13DF1C-60FB-41C2-B5B2-8F1A73CF2485
D790D359-AB3D-4657-A864-FA89FACB3E99
DF1BBC0C-4205-48E8-A1B6-EA9544D7C6E5

Is the 15th position some kind of identify as to the system that generated the uniqueidentifier?

In fact, the same thing happens with VB.net's System.Guid.Newguid function. Is the 4 a Microsoft only thing?

Edit: Perhaps I should have also asked, are they actually unique? Can one rely one them being unique in an entire database? I know database systems based on the assumption these are guaranteed to be unique within the database. With several millions records in different tables... are any of them potentially the same?

BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
Elarys
  • 639
  • 3
  • 10
  • 20

3 Answers3

11

The 4 indicates that it was generated using a pseudo-random number; See Wikipedia's article for Globally Unique Identifiers under Algorithm.

Chris Shaffer
  • 32,199
  • 5
  • 49
  • 61
  • I see it, under V4 GUIDs. It was always a curiosity of mine as I assumed them to be unique. And if one byte is always the same, how can newid produce a database unique id. Surely there must be a chance of it generating an already used one. – Elarys Feb 17 '12 at 17:28
  • 1
    The odds of generating the same number twice are quite low, and the algorithms that they use (which the 4 specifies one particular algorithm) help to reduce the odds of a duplicate even more. Think about you and a friend each flipping a coin and getting the same results each time for 122 flips in a row... – Chris Shaffer Feb 17 '12 at 17:42
  • (I got 122 from the RFC linked on wikipedia - bits 6, 7, and 12-15 are set to specific values, and the remaining bits(128-6) are randomly chosen). – Chris Shaffer Feb 17 '12 at 17:45
  • And yes, you can generally depend on them being unique; 2^122 is approximately 5.3x10^36, which leaves a lot of empty space even with millions of elements. – Chris Shaffer Feb 17 '12 at 17:57
9

It's related to the UUID/GUID version and how it's put together. Full details on Wikipedia, summary:

In the canonical representation, xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx, the most significant bits of N indicates the variant (depending on the variant; one, two or three bits are used). The variant covered by the UUID specification is indicated by the two most significant bits of N being 1 0 (i.e. the hexadecimal N will always be 8, 9, a, or b).

In the variant covered by the UUID specification, there are five versions. For this variant, the four bits of M indicates the UUID version (i.e. the hexadecimal M will either be 1, 2, 3, 4, or 5).

...

Version 4 (random)

Version 4 UUIDs use a scheme relying only on random numbers. This algorithm sets the version number as well as two reserved bits. All other bits are set using a random or pseudorandom data source.

Version 4 UUIDs have the form xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx where x is any hexadecimal digit and y is one of 8, 9, A, or B. e.g. f47ac10b-58cc-4372-a567-0e02b2c3d479.

Essentially, that digit is the version of the UUID, which explains how it was created. 4 indicates random, so the implication is that MSSQL uses random generation (vs MAC-address based, for example).

I believe, although not sure, that most MS tools and possibly the WinAPI GUID-creation functions all create version-4 UUIDs. Glancing at a scattering of COM GUIDs, this appears to be the case.

ssube
  • 47,010
  • 7
  • 103
  • 140
7

GUIDs are not completely random; they're generated according to a specific algorithm, which varies somewhat depending on the GUID version.

Specifically, when the first digit of the third group is 4, that signifies it is a v4 GUID.

Michael Madsen
  • 54,231
  • 8
  • 72
  • 83