[dba-SQLServer]Is the data a GUID?

Yeah, except if the value happens to be put into a non-uniqueidentifier
variable or field, - the result will show the datatype of that
variable/field, not of the value.  Previously posted function resolves
this issue.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco H Tapia
Sent: Friday, November 14, 2003 11:41 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Is the data a GUID?


Here is the answer to my question, I received this on the Microsoft 
newsgroup (sql server programming )

 > declare @s sql_variant
 > set @s = newid()
 > select sql_variant_property(@s,N'BaseType')

this effectively returns the data type and that is what I can use to 
validate if the data is a guid or NOT.  simply like the "IsNumeric" 
funciton.

I hope this helps out someone else :)

MartyConnelly wrote:

> 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-7AB1
> 7525F07C}/session_id~{DC1DFD4E-98B7-4E40-B3B5-DE36CA8F9FEF}/content/in
> dex.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,
>>
>


-- 
-Francisco


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com