Home > Ax Technical > Using GUID as primary key in Dynamics Ax

Using GUID as primary key in Dynamics Ax

Developers resort to use “RefRecid” as  foreign key that when they don’t find a natural primary key. But there is one another option that developers can evaluate before settling with RECID. That’s the GUID field. GUID’s are 16 byte unique identifiers that are unique across db’s unlike the recids and can be generated by the newguid() function.

Here are some of the Pro’s and cons associated with using GUID as Primary key’s.

Pro’s

-> Unique across db’s. This makes merging db’s/tables easier

-> Easy upgrade. Unlike Recid’s the GUID’s remain same after upgrades and doesn’t require any special code to handle.

-> Generation can be controlled. With GUID’s you can make the choice to insert it at any point of time unlike the recid’s. Either before insert or after insert.

-> Unlike natural keys these don’t change. The foreign key relation can be retained with ideally no change to the primary key.

Cons

-> Twice larger then recid, but SQL guru’s indicate that it matters less with good optimization for guid’s after  SQL 2005.

-> Debugging can be cumbersome

-> You can’t order them as they are not sequentially generated. (SQL has a way for sequential guid’s but I doubt if it is there in Ax also).

This  doesn’t mean that “GUID” is the way to go but that’s another option to evaluate when you make the choice. Read this article to know how to make the choice http://bit.ly/fRwJpn

In standard Ax you can find the extensive use of GUID’s in the AIF related tables.

Advertisements
  1. January 25, 2011 at 9:10 am

    I have used GUID extensively to integrate with MS CRM with Dynamics AX as CRM has the concept of GUID like AX has RecID. Nice post!!

  2. January 5, 2011 at 4:53 am

    Thanks Klaas.. We also use GUID in our features to remove the dependency on Recid. In fact in standard Ax data import is a problem in tables like DocuRef and Address. Since these tables use relations based on Recid.The probability of getting duplicates is higher with recid than GUID’s.

    I also learnt after writing here that you can generate sequential guid’s in SQL

  3. January 4, 2011 at 4:51 pm

    I use GUIDs as ID’s too in some cases. For example whene no unique key combination is available, or where a unique key has to be generated without the need to have a number sequence.

    It works pretty well, and it is way better that using recId, because linking data on recId is not a good idea (just don’t do it :-).

    The only problem I see is readability; GUIDs are hard to remember, and as you point out, GUIDs are not sequentially generated.

    An example where I use GUIDs is in buffer tables for interfaces.

    So, you’re not alone, in my opinion, in some situations, GUIDs are okay to use as key value.

    • March 11, 2013 at 10:29 am

      Hi, I wish to update my comment :-).
      In AX 2012, I would recommend using RefRecId’s for relations between records. Support has been much improved in this version to the point that’s it’s the preferred way.
      Also, be careful when using GUIDs in indexes. The other day, I had a problem with the performance of an insert statement because I had a GUID field in an index. It took 35ms to insert a record into a table, and it got worse when the table grew. As far as I understand, it takes longer to update an index that uses a GUID because GUIDs are random. It’s better to use an ID that is sequential (at least, for the performance of the insert). After removing the index on the GUID field, the insert took only 1 or 2ms.

  4. January 4, 2011 at 10:05 am

    I have seen couple of other technologies within MS Stack like Commerce Server and Biztalk using GUID’s for uniqueness. When it comes to AX, it makes sense to have GUID’s in AIF tables as it is more of messaging infrastructure. But, not all tables should go for GUID uniqueness as proper thought process should be involved before making the decision.

  1. January 4, 2011 at 5:30 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: