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.
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.
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:
17-Nov-2011: Added link to Mariano's DEX_ROW_ID post.
It is also worth to mention that the DEX_ROW_ID should not be used as key to any query destined for SSRS reporting. Typically, tables that undergo a structure change from one version to another will force a change in the DEX_ROW_ID since the prior version table is renamed to give way to the new table structure during the ugprade process. Rows of data will then be inserted into the new table from the old version's renamed table. This INSERT will not include the DEX_ROW_ID column as part of the process. Hence, all records inserted to the new table structure will acquire their own row identity value - different from the old table - causing any queries that rely on DEX_ROW_ID to return unexpected results.
For additional information on DEX_ROW_ID please take a look at my article "The DEX_ROW_ID column" at dynamicsgpblogster.blogspot.com/.../dexrowid-column.html.
Mariano Gomez, MVP
Posting from Vaidy Mohan at Microsoft Dynamics GP - Learn & Discuss
Posting from Ian Grieve at azurecurve
Posting from Mark Polino at DynamicAccounting.net
Posting by Jivtesh Singh at About Dynamics, Development and Life
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.