What will be the most effective data type to keep a UUID/GUID in databases that don't possess a native UUID/GUID data type? 2 BIGINTs?

And what will be the most effective code (C# preferred) to transform back and forth from a GUID to that particular type?


It's difficult to express what will be the most effective not understanding the database you're using.

My first inclination is always to make use of a binary(16) column.

For by using their value in C#, the System.Guid type includes a constructor that accepts a byte[] array, along with a method ToByteArray() that returns a byte array.

In my opinion, the UUID split up into two integers it's still more effective than utilizing a char area. Different DBs react diversely though. Collation could really make a difference there too. That being stated, you will find usually larger performance "sins" throughout programs, and that i don't believe that this is a significant factor in either case for a lot of programs. You will need to judge yourself based from precisely how busy is a part of your application getting? Do you want the complete quickest querying possibly by UUID? Does 600ns versus 400ns a in a major way impact on you?

If there's likely to be lots of manual sql doen using the db, then getting a vital that's composed of a UUID from separate fields type of stinks when you must do an place and there is no db default for this. That's also an issue with chars though.

For those who have a database abstraction layer, then mixing multiple table fields to obtain your UUID should not be considered a large deal.

Searching in the .NET guid class, you will find a few methods to initialize a guid:

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

Although it might be more effective, theoretically, to keep the integer inside a database (you could utilize bit shifting to actually store 4 32-bit integers.. but you'd need to calculate this out when loading and saving each time. Furthermore, it might take 4 fields within the database.. I'd imagine it might finish up being less capable.

Include the futility of reading through this directly inside your database for debugging/testing reasons, and I'd say hands-lower that it is best to store a string. It's merely a 32-character area (36 should you range from the dashes), and it is easy to transform. guid.ToString() and new Guid(stringValue);