Kbase P187107: Why does DataServer create three additional columns when migrating to MS SQL server
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/05/2011 |
|
Status: Unverified
GOAL:
Why does DataServer create three additional columns when migrating to MS SQL server
GOAL:
What are PROGRESS_RECID, PROGRESS_RECID_IDENT and PROGRESS_RECID_ALT used for?
FACT(s) (Environment):
Windows
OpenEdge 10.2x
FIX:
SQL Server does not have real sequences (until the upcoming, unreleased version of SQL Server). So if you want an identity value generated from the database, you must use an IDENTITY column. The problem is that the database, not the application, controls the value of the IDENTITY. So if you get into a situation where you rollback the deletion of a record with a IDENTITY ROWID in it, you need to restore the value of the original IDENTITY to make the record whole again. SQL Server will force you to use a new identity value if you try to insert the old record back. NOTE: There are some ways to allow the user app. to manipulate the IDENTITY, but they are bad for both performance and concurrency.
The old way of resolving this issue was to use a trigger on the insert where we used PROGRESS_RECID_IDENT to store the IDENTITY value and then assigned the value into PROGRESS_RECID inside the trigger. Then when a deletion needed to be restored, we could restore directly from PROGRESS_RECID, rather than being forced into a new ROWID by the IDENTITY column.
We dropped the trigger solution in 10.2B because it was problematic and expensive for a number of reasons. Concurrency requirements necessitate that one trigger firing for one insert be serialized by the internal latches that update the database. Along with the overhead, performance, and error recovery problems we've experience with using a trigger, it can create hot spots on disk due to the internal requirements to achieve the insert and update.
The new computed column solution, provides the same capability as the trigger but does it without all the overhead and extra server processing. It simply diverts the value of PROGRESS_RECID_IDENT to PROGRESS_RECID as part of an insert computation. It is very efficient and not external to the insert itself. Internally, when we handle the rollback of a deletion, we simple restore the PROGRESS_RECID from the PROGRESS_RECID_ALT column instead - as part of the rollback insert computation in place of the PROGRESS_RECID_IDENT. There is no way to accomplish this with a computed column without the extra column in the record.
When we get to sequences in the next SQL Server release, we will likely change this again to avoid the extra column. But in the meantime, we believe the tradeoff for the extra 8 bytes is very much worth it.