Object Relational Mappers Series - The UnGodly GUID

This is the next post on my series on ORMs.  

Since the ORM allows the app to run offline it can't really rely on an IDENTITY or SEQUENCE from the RDBMS to maintain uniqueness.  This may not be a bad thing.  For high volume applications a "create entity" operation should probably not be run against the RDBMS directly anyway.  Generally this should be queued or part of a message infrastructure.  Although an RDBMS's autoincrementing identifier technology *could* be used, you'll generally need to write your own more robust sequence generator.  These are a nightmare to write but very flexible and powerful once written. 

The ORM vendors will try to sell you on their implementation.  They have to since they are selling a disconnected data access paradigm...they can't rely on an RDBMS solution.  Oracle doesn't have the concept of "identities" and SQL Server doesn't have (until SQL 2012) the concept of "sequences."  What do the ORM vendors generally suggest?  A GUID!!! They'll tell you it is easy to create these, they are universally guaranteed to be unique, and can be generated locally by a disconnected application. 

What they won't tell you about is the cost.  A GUID is a 16 byte string.  An int is only 4.  Granted, it doesn't sound like much but consider that the GUID may be a foreign key in many tables, may have many indexes against it, cannot be remembered easily, is unwieldy, etc.  Usually the ORM tool will give you an alternative "custom id scheme".  Some solutions are better than others.  Some will allow you to create a "smart id"...one with embedded logic.  Not good, this violates normalization rules.  Some will allow you to reference an external key generator...guess what...you now have a custom written solution that I mentioned a few paragraphs ago.  So what did the ORM really give you in this case? 

Don't use a GUID if you don't have to.  Some ORM tools will allow you to utilize what they call a KeyTable.  Basically this is the equivalent of a SEQUENCE in Oracle.  In these cases the ORM will query and reserve a block of monotonically increasing integers which it will then dole out to every entity/table on an as needed basis.  So, unlike an IDENTITY, this value will be unique not just across one table, but all tables.  This is a good solution in that it reduces round-tripping to fetch ids. Use a KeyTable if you must use an ORM.