In this post we look at the dangers of choosing to use DEX_ROW_ID as a unique pointer to a record.
Don’t use DEX_ROW_ID as a persistent row identifier
Currently there is a project in progress to integrate Microsoft Dynamics GP with nopCommerce, an eCommerce web application. This involves SQL replication and transformations between schemas. My colleague that is working on the SQL integration is doing a fantastic job of optimising the integration but has had lots of questions regarding GP tables.
A issue that keeps cropping up is the temptation of the alluring DEX_ROW_ID column, a column that is found in many of the GP tables. The temptation is to use this unique (it is a primary key in the database) column as a unique identified to a row in the tables. The temptation, for instance is to store this id on the distant side of an integration and use it as a joining column to maintain the synchronisation going forward, among over temptations.
My warning to anyone doing this - DON’T as it may bite hard one day!
Why say this?
Learning from the experiences of many years of GP version upgrades (examining the scripts and what is going on) tells me this is a bad idea, because the DEX_ROW_ID column potentially be totally rebuilt with new IDs, or individual historical records may receive new IDs when using tools that can affect historical records. For example, GP upgrades often take a temporary copy of a table, then drop the original table, create a replacement new table, then insert the data back into the new table from the temp copy of the table. As the rows are inserted back into the new table, new IDs are generated as this is a SQL identify column. If the the developer is aware and prepared for this to happen, then not so bad as DEX_ROW_ID is fine for ephemeral use but not for long term persistence. It is worth mentioning as this is being discussed that this is also why custom schema objects can get lost in upgrades, as user added triggers, indexes will be lost in this rebuilding process, should that table be included in such a script during an upgrade.
DEX_ROW_ID is used by GP, internally for record locking and in that sense it uniquely identifies a column, see tempdb.DEX_LOCK table where field [row_id] refers to DEX_ROW_ID of the table row being locked.
The DEX_ROW_ID value is not persistent through upgrades and potentially some other common GP procedures that act on historical records. If for an integration you need to identify and keep track of a row, use the combined unique key for the table row instead of DEX_ROW_ID. Using the combined key is the records true unique row identifier and keeps things safe into the future.