Parent topic: Data dictionary
SQL adaptation guide For SQL SERVER 2005, 2008, 2012 / Data dictionary

NUMERIC data types

Microsoft™ SQL SERVER offers numeric data types which are quite similar to Informix® numeric data types. This table shows general conversion rules for numeric data types:

Table 1. Numeric data types (Informix vs. Microsoft SQL Server)
Informix Microsoft SQL SERVER
SMALLINT SMALLINT
INTEGER (synonym: INT) INTEGER (synonym: INT)
BIGINT BIGINT
INT8 BIGINT
DECIMAL[(p[,s])] (synonyms: DEC, NUMERIC)

DECIMAL(p,s) defines a fixed point decimal where p is the total number of significant digits and s the number of digits that fall on the right of the decimal point.

DECIMAL(p) defines a floating point decimal where p is the total number of significant digits.

The precision p can be from 1 to 32.

DECIMAL is treated as DECIMAL(16).

 DECIMAL[(p[,s)] (synonyms: DEC, NUMERIC)

DECIMAL[(p[,s])] defines a fixed point decimal where p is the total number of significant digits and s the number of digits that fall on the right of the decimal point. The maximum precision is 38.

Without any decimal storage specification, the precision defaults to 18 and the scale defaults to zero:
  • DECIMAL in SQL SERVER = DECIMAL(18,0) in Informix
  • DECIMAL(p) in SQL SERVER = DECIMAL(p,0) in Informix
MONEY[(p[,s])

SQL SERVER provides the MONEY and SMALLMONEY data types, but the currency symbol handling is quite different. Therefore, Informix MONEY columns should be implemented as DECIMAL columns in SQL SERVER.
SMALLFLOAT (synonyms: REAL) REAL
FLOAT[(n)] (synonyms: DOUBLE PRECISION)

The precision (n) is ignored.

 FLOAT(n) (synonyms: DOUBLE PRECISION)

Where n must be from 1 to 15.

Solutions

In BDL programs

When creating tables from BDL programs, the database interface automatically converts Informix numeric data types to corresponding Microsoft SQL SERVER data types.

Important: There is no SQL Server equivalent for the Informix DECIMAL(p) floating point decimal (i.e. without a scale). If your application is using such data types, you must review the database schema in order to use SQL Server compatible types. To workaround the SQL Server limitation, the SQL Server database drivers convert DECIMAL(p) types to a DECIMAL( 2*p, p ), to store all possible numbers an Informix DECIMAL(p) can store. However, the original Informix precision cannot exceed 19, since SQL Server maximum DECIMAL precision is 38(2*19). If the original precision is bigger as 19, a CREATE TABLE statement executed from a Genero program will fail with an SQL Server error 2750.

In database creation scripts

Parent topic: Data dictionary