Converting Integers to GUIDs

We know that Integers are not Guid's they are very different data types. This causes a few complications with systems like Dynamics CRM that uses Guid's for Primary Keys.

Most source systems will use an Integer as the Primary Key typically an auto-incrementing ID column on the source table.

If we could simply create a Guid from the Integer value we could keep the Integer ID column in our source system and link it to the Guid Primary Key in the Target System. No longer would we need to map the ID to a custom column or manage other lookup/conversion tables.

If we take a Guid value and replace the first 4 bytes with the bytes from an Integer we can then reliably convert an Integer to a Guid.

OK so we loose the fact that we can guarantee that every Guid value is unique, however are these values really likely to exist in our small applications?

Taking an Empty Guid and replacing the first 4 bytes with the values 1-9 produces.

00000001-0000-0000-0000-000000000000

00000002-0000-0000-0000-000000000000

00000003-0000-0000-0000-000000000000

00000004-0000-0000-0000-000000000000

00000005-0000-0000-0000-000000000000

00000006-0000-0000-0000-000000000000

00000007-0000-0000-0000-000000000000

00000008-0000-0000-0000-000000000000

00000009-0000-0000-0000-000000000000

Or Take an existing Guid and replacing the first 4 bytes with the values 1-9 produces

00000001-7df5-4947-ac47-e05ea89d21e4

00000002-7df5-4947-ac47-e05ea89d21e4

00000003-7df5-4947-ac47-e05ea89d21e4

00000004-7df5-4947-ac47-e05ea89d21e4

00000005-7df5-4947-ac47-e05ea89d21e4

00000006-7df5-4947-ac47-e05ea89d21e4

00000007-7df5-4947-ac47-e05ea89d21e4

00000008-7df5-4947-ac47-e05ea89d21e4

00000009-7df5-4947-ac47-e05ea89d21e4

So as of Data Sync 3.0.992 release we have added a new Calculated Column function NEWGUID(int) and NEWGUID(guid, int) these functions help us produce the values above.

This version will merge your integer value with an Empty Guid.

NEWGUID(ID)

This version will merge your integer value with a defined Guid value.

NEWGUID(GUID("{E03F2193-7DF5-4947-AC47-E05EA89D21E4}"), ID)

So now you can convert your source integer ID columns directly to a Guid to use with Dynamics CRM and map directly to the CRM Entity ID.