Fredrick Lackey - 44 - Oct 22, 2014

WWDD?  Anyone using GUIDs as PKs?  Doing it with SS and code-first?  Are you using anything to prevent the PK from being clustered?  I have a requirement to ensure all IDs for DTOs are GUIDs.  Contemplating whether or not to create a separate field / column for the GUID and using a long for the PK (kinda messy).

Stephen Brannan:

+Fredrick Lackey I only use Guids in my DTOs and it works great throughout SS. Only caveat I’ve ran into is defining the Id as a Guid? (Nullable Guid) for DTOs used in a GET url call since it can be optional. 

Also note that I’m not using SS.ORMLite and instead I’m using NHibernate.

There’s an [Index(Clustered=false)] attribute for normal indexes. You can use Guid as PKs, and OrmLite doesn’t emit anything to prevent Primary Keys from being clustered.

Fredrick Lackey:

+Demis Bellot Thanks, but you forgot the WWDD portion (what would Demis do?).  :P

I’ll only use Guids when absolutely needed as integer ids are easier to remember and more readable (e.g. on urls). If I only need it adhoc (i.e. for a few tables) I’d add a separate column called GlobalId, if it was a requirement that it’s on every table I’d add it as the PK.

Stephen Brannan:

+Demis Bellot But Guids make it easier for migrating data. :wink:

Right, also suitable when multiple disconnected systems need to produce unique Id’s where these datasets are merged and referenced together in an external system. Tho even in this case I’d still prefer to use urns. IMO Guid Id’s should be the exception, I personally wouldn’t expose them in user-facing systems e.g. Order/Invoice/Quote Ids.

Nicklas Laine Overgaard:

I also try to avoid using GUIDS as primary keys, simply because of the wasted space in the storage engine. There’s a good stack overflow answer on this matter:

Also, I’m suspecting that joining tables based off guids must be computationally harder for the server, as it’s comparing 128bit values (or even worse: strings, depending on the storage strategy) rather than 32bit or 64bit values - I have no performance bencmarks proving this, it’s just a hunch :slight_smile:

Nicklas Laine Overgaard:

And a really great article on issues with GUIDs as the PK:

Sir Thomas:

I have actively moved away from GUIDs as a PK whenever possible, mainly because of the visibility and troubleshooting element.  Using a lot of rest services, it was just simpler for cross referencing.  That being said, a review was first done to ensure that “data from site A” would not need to be merged with “data from site B” into single tables.

If need for export/import of data is required, then yeah, GUIDs make that easier without having to do on-import int/long gymnastics.

In many of my items, a particular use case requires fetching by a human friendly “code”, that allows you to delete/create new (or rename) and still work.

Troubleshooting-wise, GUIDs in a previous version became cumbersome to follow along the paths…