The most efficient data type for UUIDs in a database other than its own UUID

Question

What would be the most efficient data type for storing UUID / GUIDs in databases that do not have their own UUID / GUID data type? 2 BIGINTs?

And what would be the most efficient code (preferably C #) for converting to and from a GUID to this type?

Thanks.

Answer 1

It is hard to say what would be most effective without knowing which database you are using.

My first inclination would be to use the binary(16) column.

As for using this value in C #, the System.Guid type has a constructor that accepts a byte[] array, and a ToByteArray() method that returns an array of bytes.

Answer 2

In my experience, a UUID split into two integers will still be more efficient than using a char field. However, different databases react differently. Matching can also make a difference. This, as they say, usually far exceeds the "sins" of all applications, and I do not think that this would be very important for many applications. Do you have to judge yourself based on how busy this part of your application is? Do you need an absolute fast request, perhaps with a UUID? Is 600ns versus 400ns a big time difference?

If there is a lot of manual sql-doen with db, then with a key that contains the UUID of the individual fields, such as stink when you need to do an insert, and theres no db default for it. This is also a problem with characters.

If you have a database abstraction level, combining multiple table fields to get your UUID should not be a big problem.

Answer 3

Looking at the .NET guid class , there are several ways to initialize a guid:

Guid (Int32, Int16, Int16, Byte, Byte, Byte, Byte, Byte, Byte, Byte, Byte) Guid (string)

Although it is theoretically possible to store an integer more efficiently in a database (you can use bit offsets to really store 4 32-bit integers. But you would need to calculate this when loading and saving each. In addition, there would be 4 fields in the database. I I would suggest that this would be less effective.

Add inability to read this directly in your database for debugging / testing purposes, and I would say that it is best to store a string. This is just a 32-character field (36 if you include a dash) and it is very easy to convert. guid ToString () and the new Guid (stringValue);