[dba-SQLServer]Is the data a GUID?

MartyConnelly martyconnelly at shaw.ca
Thu Nov 13 20:07:26 CST 2003


I don't know if there is anything specific beyond the fact that is a 
random 16 byte binary number
and if the 13'th half byte is 4 it was generated by Microsoft (after 
they stopped using the MAC address to create it), so not truly random.
There is a guid datatype in vb.dotnet maybe check that documentation.

I suppose if you have it displayed as a hex string you could 
with/without those extraneous hyphens you could use regexp to check
but how you do that in T-SQL, I dunno.

regExp.pattern="[{][A-F0-9]{8}-[A-F0-9]{4}-[A-F0-9]{4}-[A-F0-9]{4}-[A-F0-9]{12}[}]"



Here is an interesting article on the speed of GUIDs with SQL that might 
have further info.
The Cost of GUIDs as Primary Keys
MAR 08, 2002 By Jimmy Nilsson.


http://www.informit.com/isapi/product_id~{E3D24CE5-F2A0-4B16-A39C-7AB17525F07C}/session_id~{DC1DFD4E-98B7-4E40-B3B5-DE36CA8F9FEF}/content/index.asp

A GUID is a 128-bit integer.  That's only 16 bytes.
So your 12 byte value would be 2 to the 96'th power or  2^96

One interesting bit. GUIDs are not as random as advertised.

Listing 3: Some GUIDs Generated with SQL Server 7 on NT4

B3BFC6B1-05A2-11D6-9FBA-00C04FF317DF
B3BFC6B2-05A2-11D6-9FBA-00C04FF317DF
B3BFC6B3-05A2-11D6-9FBA-00C04FF317DF
B3BFC6B4-05A2-11D6-9FBA-00C04FF317DF
B3BFC6B5-05A2-11D6-9FBA-00C04FF317DF

Listing 4: Some GUIDs Generated with SQL Server 2000 on Windows 2000

C87FC84A-EE47-47EE-842C-29E969AC5131
2A734AE4-E0EF-4D77-9F84-51A8365AC5A0
70E2E8DE-500E-4630-B3CB-166131D35C21
15ED815C-921C-4011-8667-7158982951EA
56B3BF2D-BDB0-4AFE-A26B-C8F59C4B5103

"As you saw in Listing 3, only the eighth half byte is changed between 
calls. On the other hand, in Listing 4, only the 13th half byte is 
constant between calls. In Windows 2000, the MAC address isn't used any 
longer for when GUIDs are generated. Instead, the GUID is only a 16-byte 
random number. Well, that isn't totally true. The 13th half byte is 
constant, so only 15.5 bytes are random. Adam Nathan at Microsoft 
explained to me that the 13th half byte is the value that will point out 
the source of the GUID, and 4 means Microsoft."



Francisco H Tapia wrote:

> Anybody have a TSQL test on weather the data is a GUID or not?
> thanks....
>
> I want to test some data, I have the function for ISnumeric which 
> gives back a simple 1 or 0 if data is numeric or not, but does not 
> tell you if the data is a GUID (or that it qualifies as a number).
>
> Thanks,
>

-- 
Marty Connelly
Victoria, B.C.
Canada





More information about the dba-SQLServer mailing list