Choosing Data Types
An SQL column definition specifies the characteristics of the data in an SQL column. It is much more precise than a data definition in Reality. In particular, it contains column definition fields which specify:
-
Column Type (field 1) - The type of data allowed in the column.
-
Precision (field 3) - The maximum length of data allowed in the column.
-
Scale (field 4) - The number of decimal places allowed in a DECIMAL or NUMERIC column.
When converting an existing data definition to a column definition, SQLM inserts a data type and size based on the contents of the Reality data. Alternatively, if there are no data definitions, SQLM creates column definitions based on a default definition. In each case, it is necessary to check that the fields in the newly created column definition describe as closely as possible the actual SQL data defined by the column definition.
Column Type
The column type (column definition field 1) identifies the type of data that the column may contain. Unlike Reality attributes, which do not have types, SQL needs information about the type of data that may occur in an attribute.
The column type can be specified by name or an integer value. Valid type names and numbers are listed in SQL Column and Index Definitions.
After carrying out the SQLM conversion, you must check that the column type describes as closely as possible the actual data that will occur in the column. Note that the column type must describe the data as seen by SQL. In some cases, this may not correspond to the data type of the underlying Reality attribute. In particular, when a Data Generation conversion is applied, SQL processes the data after the conversion, the data type of which may be different from that of the Reality data.
Having established the type of data seen by SQL, enter it as a name (case not significant) or as a number, as shown above. The following guidelines can be used to select the data type:
-
If the data will always be a numeric integer, select one of the integer types:
-
TINYINT for values between -128 and 127 inclusive.
-
SMALLINT for values between -32,768 and 32,767 inclusive.
-
INTEGER for values between -2,147,483,648 and 2,147,483,647 inclusive.
-
BIGINT for values less than -2,147,483,648 or greater than 2,147,483,647.
-
-
If the data will be numeric but may include a decimal fraction, select DECIMAL, NUMERIC, REAL, FLOAT or DOUBLE, depending on the required precision and the expected range of values.
-
For Boolean (true/false) values, use BIT.
-
If the data may contain any character, but will always have a fixed length of up to 254 characters, select CHAR.
-
For variable length data up to 254 characters in length, containing any character, use VARCHAR.
-
For data that may be longer than 254 characters, use LONGVARCHAR.
-
For dates in Reality internal format only, use DATE.
-
For times in Reality internal format only, use TIME.
-
For combined dates and times in Reality internal format only, use TIMESTAMP.
-
Strings cannot be compared with numbers, dates or times. If a column containing one of these data types is likely to be used in string comparisons, you should consider using one of the string types (CHAR, VARCHAR or LONGVARCHAR).
Note
SQLM does not currently support the binary data types: BINARY, VARBINARY and LONGVARBINARY.
Precision
The precision of a column defines the column width or, for VAR types, the maximum length of the string. The precision of the column must be set according to the data type, as listed in SQL Column and Index Definitions.
Scale
Scale defines the number of digits to the right of the decimal point for DECIMAL and NUMERIC column types. Note that this is only applicable to DECIMAL and NUMERIC data.