[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-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,
>>
>


-- 
-Francisco