The DEX_ROW_ID column
From time to time the question comes up in newsgroups, informal conversations between developers, and surprise phone calls. The questions come in many flavors:
To start unreeling these questions it is best to start with two key concepts: active and passive record locks.
Active Locks
An active lock allows other users to read a table record, but not make any changes or delete the record. Active locking ensures that the user who has the active lock is the only user who can make changes or delete the record. If other users try to delete or change the record, a table-sharing error will occur. An active lock is applied each time a record is read using the Dexterity change or edit table statements with the lock keyword included.
Passive Locks
A passive lock allows other users to access the record. Other users can delete the record or make changes to it. Passive locking ensures that other users accessing the record can be made aware that the record has been deleted or that the contents of the record have changed. A passive lock is applied each time a record is read using the change or edit table statement.
What is the DEX_ROW_ID?
As part of Dexterity's table definition requirements, active locking must be enabled on a per-table basis by marking the Allow Active Locking option in the table’s Table Definition window.
For both active and passive locking to work properly in a Dexterity-based application, such as Microsoft Dynamics GP, every SQL table must include a column that is used to track the identity of individual records being locked. This column is the DEX_ROW_ID.
Why do all Dynamics GP's SQL Server tables have a DEX_ROW_ID?
When Dexterity's Runtime Engine creates a table, the DEX_ROW_ID column is added to each table created automatically. In addition, this column is hidden to the table definition within Dexterity application.
If tables are created through a method other than allowing the Dexterity runtime engine to create them, then the DEX_ROW_ID column must be added via SQL Server Management Studio or using the Transact-SQL (T-SQL) ALTER TABLE statement, as follows:
Dexterity uses the DEX_ROW_ID column internally. Developers need not to manipulate the column at all.
If tables are created outside of the Dexterity environment and do not include the DEX_ROW_ID column in each of your tables, active locking will not function properly for that table.
How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?
From a pure SQL Server perspective, the DEX_ROW_ID is an INT type column with the IDENTITY property assigned to it. This enables the developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers. When values are inserted into a table that has an identifier column, SQL Server automatically generates the next identity value by adding the increment to the seed. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table, with the seed and increment values applied in the order that those rows were originally inserted. Identity numbers are also generated for any new rows that are added. You cannot modify an existing table column to add the IDENTITY property.
Identity columns and their values are managed at the database level and are not controlled by any applications built in Dexterity.
Can I build reports using the DEX_ROW_ID column?
While there is nothing preventing report developers from using the DEX_ROW_ID column, it is strongly recommended to avoid using the values as a method to uniquely identify any given piece of information.
Tables with frequent delete operations will generate gaps between DEX_ROW_ID column values. Deleted DEX_ROW_ID values are not reused.
In addition, during a Dynamics GP upgrade, if a table upgrade changes, the upgrade routines will host the data in a temporary table, the original table will be dropped and recreated, and in the process the DEX_ROW_ID column will be re-seeded. When data is brought back into the upgraded table, the rows will be assigned a new DEX_ROW_ID value as inserted.
Summary
The DEX_ROW_ID column is only useful to Dexterity applications as it provides a way to manage active and passive locking of records, but should no be relied upon for report development, or external processes.
Other Resources
Dynamics GP Blogster - Know thy common table operations with Dexterity - Part I.
Microsoft Developers Network - SQL Server Books Online
Until next post!
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
- What is the DEX_ROW_ID?
- Why do all Dynamics GP's SQL Server tables have a DEX_ROW_ID?
- How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?
- Can I build reports using the DEX_ROW_ID column?
To start unreeling these questions it is best to start with two key concepts: active and passive record locks.
Active Locks
An active lock allows other users to read a table record, but not make any changes or delete the record. Active locking ensures that the user who has the active lock is the only user who can make changes or delete the record. If other users try to delete or change the record, a table-sharing error will occur. An active lock is applied each time a record is read using the Dexterity change or edit table statements with the lock keyword included.
Passive Locks
A passive lock allows other users to access the record. Other users can delete the record or make changes to it. Passive locking ensures that other users accessing the record can be made aware that the record has been deleted or that the contents of the record have changed. A passive lock is applied each time a record is read using the change or edit table statement.
What is the DEX_ROW_ID?
As part of Dexterity's table definition requirements, active locking must be enabled on a per-table basis by marking the Allow Active Locking option in the table’s Table Definition window.
For both active and passive locking to work properly in a Dexterity-based application, such as Microsoft Dynamics GP, every SQL table must include a column that is used to track the identity of individual records being locked. This column is the DEX_ROW_ID.
Why do all Dynamics GP's SQL Server tables have a DEX_ROW_ID?
When Dexterity's Runtime Engine creates a table, the DEX_ROW_ID column is added to each table created automatically. In addition, this column is hidden to the table definition within Dexterity application.
If tables are created through a method other than allowing the Dexterity runtime engine to create them, then the DEX_ROW_ID column must be added via SQL Server Management Studio or using the Transact-SQL (T-SQL) ALTER TABLE statement, as follows:
ALTER TABLE dbo.TableName ADD DEX_ROW_ID INT IDENTITY;
GO
Dexterity uses the DEX_ROW_ID column internally. Developers need not to manipulate the column at all.
If tables are created outside of the Dexterity environment and do not include the DEX_ROW_ID column in each of your tables, active locking will not function properly for that table.
How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?
From a pure SQL Server perspective, the DEX_ROW_ID is an INT type column with the IDENTITY property assigned to it. This enables the developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers. When values are inserted into a table that has an identifier column, SQL Server automatically generates the next identity value by adding the increment to the seed. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table, with the seed and increment values applied in the order that those rows were originally inserted. Identity numbers are also generated for any new rows that are added. You cannot modify an existing table column to add the IDENTITY property.
Identity columns and their values are managed at the database level and are not controlled by any applications built in Dexterity.
Can I build reports using the DEX_ROW_ID column?
While there is nothing preventing report developers from using the DEX_ROW_ID column, it is strongly recommended to avoid using the values as a method to uniquely identify any given piece of information.
Tables with frequent delete operations will generate gaps between DEX_ROW_ID column values. Deleted DEX_ROW_ID values are not reused.
In addition, during a Dynamics GP upgrade, if a table upgrade changes, the upgrade routines will host the data in a temporary table, the original table will be dropped and recreated, and in the process the DEX_ROW_ID column will be re-seeded. When data is brought back into the upgraded table, the rows will be assigned a new DEX_ROW_ID value as inserted.
Summary
The DEX_ROW_ID column is only useful to Dexterity applications as it provides a way to manage active and passive locking of records, but should no be relied upon for report development, or external processes.
Other Resources
Dynamics GP Blogster - Know thy common table operations with Dexterity - Part I.
Microsoft Developers Network - SQL Server Books Online
Until next post!
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
Comments
I completely agree with your point. DEX_ROW_ID should never be used as a reference for any Reporting or Coding.
Your article is another handy reference for developers whom often overlook the importance and scope of DEX_ROW_ID.
Thanks
Vaidy
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hi Mariano
Good article.
Just thought I would mention one point that is missing.
The Dex Row ID is used automatically added by dexterity to indexes created to ensure that there is a unique index on the table.
For Example the GL20000 table
David
David Musgrave
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific
Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics
Tel (IP): +61 8 9420 8475 or +61 2 8817 9175
Tel: +61 8 9332 9446
Fax: +61 8 9332 9464
mailto:David.Musgrave@microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP
This email is intended solely for the use of the addressee and may contain information that is confidential or subject to legal professional privilege. If you receive this email in error please immediately notify the sender and delete the email.
There very little material out there on how to transactional implement replication with having to creating a ROWID column. Is it possible to use DEX ROW id and implement transactional replication.
For more information on transactional replication of identity columns take a look at:
Replicate Identity Columns
MG.-
Now that we have upgraded GP at our Australian branch, their DEX_ROW_ID values have changed and I am having to reload all of the tables - 500,000 rows for SOP30300 alone.
Is there a more appropriate way to identify transactions that have been added to the tables?
I have same issue for GL30000 and GL20000 for data warehouse.
I have to load all data in each ETL or delete DEX_ROW_ID from fact table.
Regards
Niasha