What is the reason for setting SQL-width?

Product: OpenEdge
Version: All supported versions
OS: All supported platforms
Other: SQL-92, _SQLSRV2
What is the reason for setting SQL-WIDTH?
What is upper limit for SQL-WIDTH?
SQL-WIDTH is mainly designed for solving the incompatibility between 4GL and SQL92. 4GL Data is variable length, but SQL data is fixed length. The fact that SQL data has an upper limit is in accordance to the international SQL92 standard.

In order to access the ABL/4GL database through SQL92, SQL-width has to be used to define the SQL maximum length for ABL fields. It normally applies to character, decimal and varbinary ("raw" type in ABL) types. The SQL-width can be modified through the 4GL "Data Dictionary", DBTOOL or programatically (_field._Data-Type, _field._width) as the size of ABL record data increases.

Upper limits on SQL-width for data types:

4GL Type              Max  Value         SQL-Width Calculation.
-----------------     ----------------    --------------------------
character/raw         31995              2    *  "4GL format"
decimal               50 (precision)     15  +  "4GL decimals"

Why not define SQL-Widths for all Character fields as 31995 (maximum size allowed)?

_SQLSRV2 processes will consume more memory than expected as a consequence.

31995 is not only the maximum allowed for a character field, it is also the maximum size of a record The SQL engine will allocate memory for sqlda structure (which will hold result set to be passed back to client) based on SQL width only. A data structure is built to hold 50 rows of the result set to be passed back to the client (default Fetch Array Size, which can be tuned by the client application). 

When all character fields has sql width of 31995, the SQL engine will allocate:
  • For single-byte charset: 
  • 31995 * < number of character fields > * 50 (== fetch size)  bytes of memory
  • For multi-byte charsets:
  • more memory will be allocated as the sql-width defines max size in terms of characters and a character may require more than one byte.
Better SQLSRV memory use can be achieved by:
  • Adjusting the SQL width to reflect actual max sizes of the various varchar columns 
  • Reducing fetch size. The Fetch Array Size setting is located on the Advanced tab.
  • Consider using the SUBSTR function on these character fields in SELECT list. This will force the SQL engine to allocate memory based on arguments to SUBSTR instead of sql-width (31995).
For example ,  the SQL engine will allocate memory to hold 11 characters instead of 31955
SUBSTR(col1, 1,10)
4/18/2016 12:44 PM