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.
Share this:
Leave a comment
Dynamics Ax News
- Emerging Trends of Microsoft Dynamics AX Consulting Service Market | Chetu, Merit Solutions, IBM, AxPact - Amoré
- Business Applications - Microsoft
- Top Microsoft Dynamics ERP & CRM News of 2023 - MSDynamicsWorld
- Paystand Brings Full Payments Integration to Microsoft Business Central, Including NAV, GP and AX - Business Wire
- Microsoft aims for Dynamics 365 cloud migrations - TechTarget
- Paystand unlocks finance integrations with Microsoft Dynamics 365 - ERP Today
- [Trends ] Microsoft Dynamics ERP Consulting Service Market Growth Drivers | Alta Vista Technology, Innovia ... - Amoré
- Paystand Integrates B2B Payments With Microsoft Dynamics 365 Business Central - PYMNTS.com
- Microsoft Dynamics vs Salesforce (2024 Comparison) – Forbes Advisor - Forbes
- Microsoft offers Dynamics users fresh incentives to move to the cloud - CIO
Visitors Info
Ax Community
On the Web
Ax blogs stream
- An error has occurred; the feed is probably down. Try again later.
Casperkamal's Dynamics Ax blog 
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!!
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
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.
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.
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.