Column format for storing GUID?

Hello,
What would be the best way of storing a GUID/UUID in a column, for
efficient indexing and lookup?
varchar(32)? int?
Thanks,

-Steve

On Mar 8, 6:54 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 8 Mar 2007 16:46:54 -0800, estebanj...@gmail.com wrote:
>
> > Hello,
> > What would be the best way of storing a GUID/UUID in a column, for
> > efficient indexing and lookup?
> > varchar(32)? int?
>
> Two ints. Unless you REALLY KNOW that GUID is going to be used equally
> often as UUID. (In my own work, user ID is important far more often than
> group ever comes up. Your application may vary from this.)
>
> --
> 85. I will not use any plan in which the final step is horribly complicated,
> e.g. "Align the 12 Stones of Power on the sacred altar then activate the
> medallion at the moment of total eclipse." Instead it will be more along
> the lines of "Push the button." --Peter Anspach's Evil Overlord List

Ah... by "GUID" I meant 128-bit Globally Unique Identifier (the same
thing as Universally Unique Identifier), not Group ID.

So, the question is what is the conventional way that people store 128-
bit IDs in their database tables? (I'm new to database design, so)
One could convert these to hexadecimal representation and store in a
varchar(32) column, but I don't know that that's an efficient use of
the space/comparison. I could split 128-bit into two BIGINT columns,
but I really don't want to use 2 columns for a single ID.

How do database experts store GUIDs in database tables? I plan to
index these GUIDs.

-Steve

estebanjang@gmail.com wrote:
>
> So, the question is what is the conventional way that people store 128-
> bit IDs in their database tables? (I'm new to database design, so)
> One could convert these to hexadecimal representation and store in a
> varchar(32) column, but I don't know that that's an efficient use of
> the space/comparison. I could split 128-bit into two BIGINT columns,
> but I really don't want to use 2 columns for a single ID.
>
> How do database experts store GUIDs in database tables? I plan to
> index these GUIDs.

If you can afford the extra work for assembling/dissembling - use two
BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
allow you to input/output the number as a whole.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On Fri, 9 Mar 2007 11:13:34 +0100, Axel Schwenke
<axel.schwenke@gmx.de> wrote:

>estebanjang@gmail.com wrote:
>>
>> So, the question is what is the conventional way that people store 128-
>> bit IDs in their database tables? (I'm new to database design, so)
>> One could convert these to hexadecimal representation and store in a
>> varchar(32) column, but I don't know that that's an efficient use of
>> the space/comparison. I could split 128-bit into two BIGINT columns,
>> but I really don't want to use 2 columns for a single ID.
>>
>> How do database experts store GUIDs in database tables? I plan to
>> index these GUIDs.
>
>If you can afford the extra work for assembling/dissembling - use two
>BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
>allow you to input/output the number as a whole.
>
>
>XL
Given that GUIDs are most often written as a sequence of hexadeciamal
digits "seperated" by hyphens, isn't storing these as anything other
than "char" going to mean that "the extra work for
assembling/dissembling" will always be required?

Just a thought
Lee


From Wikipedia

Guids are most commonly written in text as a sequence of hexadecimal
digits such as:

3F2504E0-4F89-11D3-9A0C-0305E82C3301

This text notation follows from the data structure defined above. The
sequence is

1. Data1 (8 characters)
2. Hyphen
3. Data2 (4 characters)
4. Hyphen
5. Data3 (4 characters)
6. Hyphen
7. Initial two items from Data4 (4 characters)
8. Hyphen
9. Remaining six items from Data4 (12 characters)

Often braces are added to enclose the above format, as such:

{3F2504E0-4F89-11D3-9A0C-0305E82C3301}

Lee Peedin <lpeedinDONOTSPAME@nc.rr.com> wrote:
> On Fri, 9 Mar 2007 11:13:34 +0100, Axel Schwenke
> <axel.schwenke@gmx.de> wrote:
>
>>> How do database experts store GUIDs in database tables? I plan to
>>> index these GUIDs.
>>
>>If you can afford the extra work for assembling/dissembling - use two
>>BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
>>allow you to input/output the number as a whole.
>>
>>
> Given that GUIDs are most often written as a sequence of hexadeciamal
> digits "seperated" by hyphens, isn't storing these as anything other
> than "char" going to mean that "the extra work for
> assembling/dissembling" will always be required?

Hmm. I read about that GUID stuff now and the "hex-with-hyphens" format
looks quite arbitrary to me. Since it is basically a 128-bit number,
I would prefer to store it as such.

> {3F2504E0-4F89-11D3-9A0C-0305E82C3301}

Of course one could store it as CHAR(36) or even CHAR(38) - but that
would mean:

- waste of storage space, resulting in
- performance impact
and last not least
- possibility to have invalid GUIDs - we need to check integrity


The "silver bullet" would be a native 128-bit INTEGER type and a GUID
data format a'la SELECT CONVERT(uid_col AS GUID) FROM ...


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

what operations do you need to do on it?

until there is a native GUID format, it seems like char(36) would be a
good choice, given that MySQL's UUID() function returns a 36-character
text format (hex w/ hyphens), and I can't see why there would be any
other operations on it besides generation & comparison

Jason Sachs wrote:
> what operations do you need to do on it?
>
> until there is a native GUID format, it seems like char(36) would be a
> good choice, given that MySQL's UUID() function returns a 36-character
> text format (hex w/ hyphens), and I can't see why there would be any
> other operations on it besides generation & comparison
>

It's simple. Comparison on a 128 bit integer is much faster than
comparison on a 36 (or 38) byte character stringl

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mar 9, 6:32 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> > On Fri, 9 Mar 2007 11:13:34 +0100, Axel Schwenke
> > <axel.schwe...@gmx.de> wrote:
>
> >>> How do database experts store GUIDs in database tables? I plan to
> >>> index these GUIDs.
>
> >>If you can afford the extra work for assembling/dissembling - use two
> >>BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
> >>allow you to input/output the number as a whole.
>
> > Given that GUIDs are most often written as a sequence of hexadeciamal
> > digits "seperated" by hyphens, isn't storing these as anything other
> > than "char" going to mean that "the extra work for
> > assembling/dissembling" will always be required?
>
> Hmm. I read about that GUID stuff now and the "hex-with-hyphens" format
> looks quite arbitrary to me. Since it is basically a 128-bit number,
> I would prefer to store it as such.
>
> > {3F2504E0-4F89-11D3-9A0C-0305E82C3301}
>
> Of course one could store it as CHAR(36) or even CHAR(38) - but that
> would mean:
>
> - waste of storage space, resulting in
> - performance impact
> and last not least
> - possibility to have invalid GUIDs - we need to check integrity
>
> The "silver bullet" would be a native 128-bit INTEGER type and a GUID
> data format a'la SELECT CONVERT(uid_col AS GUID) FROM ...
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

Thanks. Any chance that MySQL will provide this "silver bullet" at
some point? ;-)

Looking through MSDN, Microsoft seems to have UNIQUEINDENTIFIER data
type. This is a 16-byte identifier.
Combine that with NEWID( ) function and ROWGUIDCOL constraint, and I
have exactly what I am looking for.

-Steve