Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6500 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:11238
Title:The DEX_ROW_ID column
URL:http://dynamicsgpblogster.blogspot.com/2008/12/dexrowid-column.html
Description:From time to time the question comes up in newsgroups, informal conversations between developers, and surprise phone calls. The questions come in many flavors:

  • 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
Category:DEVELOPMENT: Dexterity
Link Owner:
Date Added:June 17, 2010 06:13:47 PM
Number Hits:8
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.