Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
The DataServer Tutorial : Prime to ROWID identification : ROWID sub-section of MS SQL Server Conversion
 

ROWID sub-section of MS SQL Server Conversion

The following figure shows the sub-section of the OpenEdge to MS SQL Server conversion dialog boxes.
When you select the Migrate Constraints option above the ROWID sub-section and you define a primary constraint definition or clustered mock constraint definition, or both for a migrated table, Migrate Constraints takes precedence over any selection made in the ROWID sub-section for ROWID designation.
A clustered index is always implied when a primary key is specified and when an explicit clustered index is not specified. Also, by not setting the primary index to the clustered explicitly, you can specify separate indexes for one or other clusters without any conflicts.
Figure 11. ROWID sub-section
The behavior resulting from selecting the various elements of the figure above is as follows:
*Try Primary for ROWID
*Not selected (default) — Do not use the primary index mapping of this feature. Left unselected, this option is backward compatible, or you can choose other options to determine index selections and ROWID designation.
*Selected — Whenever available, use the OpenEdge primary index to establish index selections for primary and/or clustered index and attempt to make the ROWID designation from the same index if uniqueness can be obtained from the key. This option takes precedence, when you select this option along with other options in the ROWID sub-section.
*Create RECID Field using
This option is selected by default. Select this option for backward compatibility with the defaults from previous OpenEdge releases. This option when used with the Select 'Best' ROWID Index option uniquify the the non-unique indexes. This option is enabled if any of the two mutually exclusive options, Create RECID Field - For ROWID Uniqueness and Create RECID Field - For, are selected.
*Trigger (default) — Create PROGRESS_RECID/PROGRESS_RECID_UNIQUE using the legacy trigger mechanism. Select this option for backward compatibility.
*Computed Column — Create PROGRESS_RECID/PROGRESS_RECID_UNIQUE using a computed column. This is preferred method for creating PROGRESS_RECID/PROGRESS_RECID_UNIQUE for producing index uniqueness and ROWID capability, but comes with the restriction that no server-based INSERT trigger can exist on the table.
*Create RECID For
Selected by default, this option is disabled when the Create RECID For ROWID Uniqueness is selected. The two options are mutually exclusive.
Also, when this option is unselected, the legacy process of generating the PROGRESS_RECID field and assigning it to ROWID is removed.
*ROWID (default) — When selected without the Migrate Constraints and the Try Primary for ROWID options selected, this option performs the legacy task of the Create RECID Field option from previous releases.
*When selected in conjunction with Migrate Constraints and/or the Try Primary for ROWID options, this option becomes a secondary solution for generating ROWID for non-unique primary and/or clustered indexes that could not be designated for ROWID.
*Prime ROWID — When selected without the Migrate Constraints and the Try Primary for ROWID options selected, this option performs the legacy task of the Create RECID Field option from previous releases.
*When selected in conjunction with Migrate Constraints and/or the Try Primary for ROWID options, this option becomes a secondary solution for generating ROWID for non-unique primary and/or clustered indexes that could not be designated for ROWID. The ROWID index is optimized by designating the index as the primary and clustered as well.
*Create RECID For ROWID Uniqueness
Unselected by default, this option is enabled when Create RECID For is unselected. The two options are mutually exclusive. This option works in conjunction with either of the two options, Try Primary for ROWID and Select 'Best' ROWID Index.
*Unselected (default) — Does not compensate for missing uniqueness of the existing indexes by appending PROGRESS_RECID_UNIQUE identity column for ROWID designation purposes.
*Selected — Whenever the targeted primary and/or clustered index is non-unique and therefore cannot be designated for the ROWID index, generate the IDENTITY column and append it as the last index component of the ROWID composite in order to create uniqueness. Effectively, this provides a DataServer replacement for the 4-byte internal RID that MS SQL Server otherwise requires on the primary and/or clustered indexes (but is inaccessible to DataServers). It also makes the designated primary and/or clustered index now eligible for ROWID candidacy during migration.
When the RECID field is used in this way, it is named PROGRESS_RECID_UNIQUE, and not PROGRESS_RECID. The name change allows it to be utilized by the trigger and/or computed column, and be populated on CREATE, but the name uniqueness prevents it from being identified as a ROWID candidate by itself from the dictionary utilities.
*Select 'Best' ROWID INDEX
Unselected by default, this option is mutually exclusive with the Create RECID Field For options. If the Create RECID Field For options are unselected, this option becomes mandatory.
When selected and used in conjunction with Migrate Constraints and/or Try Primary for ROWID, it becomes a secondary path in the search for the designated ROWID index. The primary and clustered selections associated with those options will take priority in ROWID selection.
Note: This is a schema "pull" option, not a database "push" option and is therefore unaffected by the Create RECID Field - For ROWID Uniqueness when that option is selected.
*Using OE Schema (default) — Locate an existing index to use as ROWID, if it qualifies, according to the requirements of the OE Schema. The criteria of the OE Schema is described in Best index selection. If a Best index selection can be found in the OE Schema, it is designated as ROWID. Selecting this option provides backward compatibility with earlier releases when those legacy migrations had the Create RECID Field unselected.
In case none of the existing indexes are unique and you have selected the For ROWID Uniqueness option, then this option evaluates indexes for ROWID designation by making unique the existing indexes. The identity column will be appended to the 'best' index selected for ROWID designation.
*Using Foreign schema — Locate an existing index to use as ROWID from the ODBC API that evaluates best index on the server. If a Best Index selection can be found through the ODBC API, it is designated as ROWID.
* Best index selection