Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Initial Programming Considerations : Database design issues : Case-sensitivity


By default, an OpenEdge database is case-insensitive. An MS SQL Server database is also case-insensitive by default, which is why the Insensitive field in the OpenEdge DB to MS SQL Server Conversion tool is set to "yes". Using case-insensitivity in both OpenEdge and MS SQL Server enables seamless compatibility between the two, and provides the best performance and least maintenance. Whether or not your data source code page is case-sensitive, you can set the attributes of any field to be either case-sensitive or case-insensitive.
If you intend to use case sensitivity, consider the following:
*Pattern-matching literals in data source access statements retrieve case-sensitive data.
*The OpenEdge database considers the user ID and password submitted at connection time to be case-sensitive.
*If you define a case-sensitive code page in your foreign data source, you must set the Insensitive field in the OpenEdge DB to MS SQL Server Conversion dialog to "no". Case-insensitive columns, in your case-sensitive database migration, are given shadow columns to support case-insensitivity in the foreign data source if they participate as a component in an index.
After the migration process, if you perform a schema-pull operation, the columns migrated as case insensitive that were used as index components get appropriately pulled as case-insensitive columns into the schema holder by virtue of the shadow columns indicating case-insensitivity. But, the other columns that did not participate in indexes do not get any indication from MS SQL Server of their case-insensitivity. When the OpenEdge database migration to MS SQL Server sets the Insensitive indicator in the schema holder to "no", the foreign code page is assumed to be case-sensitive, so, when the Update/Add Table Definitions option is selected during the schema-pull operation to pull table definitions directly from a MS SQL Server, the unindexed table columns are assumed to be case-sensitive, even if they were migrated as case-insensitive and you had not made any changes to the table definitions you pulled. One side effect is that if your r-code was compiled before the schema pull operation, it would now have a schema mismatch with the pulled server definitions. This is because the r-code takes into account case-sensitivity during the schema check when files are opened at run-time.
To avoid schema mismatch during r-code execution, run the Adjust Schema utility against the original OpenEdge database after the Update/Add Table Definitions operation on tables that were originally migrated with unindexed case-insensitive columns. This causes the schema holder to be adjusted with the case-insensitive attribute of the column originating from the migrated OpenEdge database.
If an indexed field is case-insensitive, an OpenEdge database does not distinguish between uppercase and lowercase letters for that index when sorting or matching data. In general, this flexibility in an application makes data entry easier for end users because they can enter lowercase or uppercase versions of an index. However, if you want to enforce an uppercase/lowercase distinction in your applications, set the attribute to case sensitive.
If you are using a case-sensitive code page, the DataServer can make this feature compatible across OpenEdge and MS SQL Server data sources. To support case insensitivity with a case-sensitive code page, an extra column, known as a shadow column, must be added to the data source immediately before the indexed column. This column is named _S#_column. See Migrating an OpenEdge database to MS SQL Server for instructions on adding this column automatically with the OpenEdge DB to MS SQL Server utility.
Note: By default, sort order in MS SQL Server is not case sensitive.