Freigeben über

Do not use the DEX_ROW_ID table column in your Customizations and Integrations

  • Artikel

David Meego - Click for blog homepageToday, 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.

Comments

  • Anonymous
    November 16, 2011
    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. MG.- Mariano Gomez, MVP

  • Anonymous
    November 16, 2011
    Posting from Vaidy Mohan at Microsoft Dynamics GP - Learn & Discuss www.vaidy-dyngp.com/.../perils-of-using-of-dexrowid-on.html

  • Anonymous
    November 16, 2011
    The comment has been removed

  • Anonymous
    November 16, 2011
    Posting from Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com/.../dave-musgrave-reminds-us-do-not-use.html

  • Anonymous
    November 21, 2011
    Posting by Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../if-you-are-looking-for-information.html

  • Anonymous
    April 19, 2016
    We use the DEX_ROW_ID column to identify transactions to copy across to our data warehouse. We copy all items where the DEX_ROW_ID value in the source table is greater than the max DEX_ROW_ID value in the data warehouse table.We upgraded to GP2015 at our Australian branch and all of their DEX_ROW_ID values in the sales tables have changed.It is a big job to re-copy ALL transactions (500,000 rows in SOP30300 alone).Is there a more appropriate way to identify added rows in the GP tables?

    • Anonymous
      April 19, 2016
      Hi Andrew, Lucky that I still monitor comments on my old blog.Many of the tables have a DEX_ROW_TS column which is populated via an update trigger with a full datetime stamp in UTC (GMT).This was created specifically for integrations and data warehousing as it will not change (as it is a data column not an identity column like DEX_ROW_ID) and it is timezone independent as it is always in UTC.Hopefully using DEX_ROW_TS will help you resolve your issue.DavidWinthrop Development Consultantshttp://www.winthropdc.com
      • Anonymous
        April 19, 2016
        PS: DEX_ROW_TS is updated for both insert and update events, so it will work better than just detecting new records using DEX_ROW_IDs with a higher than previous value.
        • Anonymous
          April 19, 2016
          Thanks very much Dave - I don't understand why our local GP consultants weren't able to be this helpful.Thanks for your assistance.Andrew
          • Anonymous
            April 20, 2016
            Just to confirm:Transactions in GL20000: is there any chance that a transaction could change once it has been posted? If I import transactions with a later timestamp that what exists in the DW, I will only import new transactions, and not changes to existing transactions?Same with SOP30200/SOP30300 - these are all posted transactions and will not change, right?There is no timestamp for IV00102 - is there a way to identify lines that are closed and which won't change? Do I just separate the import by looking at the QTYONHAND value and re-import lines that were not previously Nil? For INV10200 can I use the RCPTSOLD flag for this?
            • Anonymous
              April 28, 2016
              Hi AndrewIf there is no DEX_ROW_TS on the table then you can't use that method... but you could add a SQL Trigger to store a DEX_ROW_TS column in a parallel table with the key fields which you could then join to the original table and get the same effect.DavidPS: Posted transactions should not change.