Do not use the DEX_ROW_ID table column in your Customizations and Integrations
Today, I responded on a post on the Partner Forum asking "Can the DEX_ROW_ID change for a record over time?". In this case the partner had seen the values for DEX_ROW_ID change on the GL20000 (GL_YTD_TRX_OPEN) and GL30000 (GL_Account_TRX_HIST) tables. This caused problems for an integration to an external system that used the DEX_ROW_ID as a key field.
Use of the DEX_ROW_ID as a key field relies on the assumption that the DEX_ROW_ID will remain the same for ever after it has been assigned to a row of data. Sadly, this assumption is not valid as the DEX_ROW_ID, while being fairly static, can change.
History
When support for Microsoft SQL Server was added Microsoft Dynamics GP (Then Great Plains Software Dynamics 3.15), the development team needed a way to map all of the ISAM table functionality (currently supported on Ctree and Btrieve/Pervasive.SQL) to work with SQL. As part of this process, the DEX_ROW_ID column was added.
The DEX_ROW_ID identity column was added to Dexterity tables at the SQL Server level because some Dexterity Table Definitions did not contain a unique (No Duplicates Allowed) index/key. By adding the DEX_ROW_ID to the index columns, a unique primary key for SQL could be created.
The DEX_ROW_ID is also used for Dexterity's Optimistic Concurrency Control (OCC), which is how two users can update the same record of data at the same time as long as they don't change the same field. This takes the original page locking or current row level locking and provides the equivalent of field level lock. The DEX_LOCK and DEX_SESSION tables in the tempdb SQL database are used as part of the OCC functionality.
The DEX_ROW_ID is really for internal use only and should not be used as a key field in any customizations or integrations. Early Analytical Accounting code used the DEX_ROW_ID as a key field and this had to be changed because the DEX_ROW_ID is not guaranteed to stay constant.
Conclusion
Any process which moves data can cause the DEX_ROW_ID values to change. A common example of this would be changing of a table structure during an upgrade. All the data is copied into a temporary table, then the table structure is changed (by dropping and recreating the table) and the data copied back. This will then renumber all the DEX_ROW_ID values from 1 again.
In Summary: You would be best using the primary key fields from the table in your customizations & integrations and not the DEX_ROW_ID.
For more information, have a look at Mariano Gomez's article over at The Dynamics GP Blogster blog:
David
17-Nov-2011: Added link to Mariano's DEX_ROW_ID post.