Data Types

A data type is an attribute of every column in a table that specifies the type of data the column stores. For example, the data type is the characteristic of a piece of data that says it is a character string, an integer, a floating-point number, or a date or time. The data type also determines how much memory to allocate for the column’s value.

The following table lists the data types that are supported by FedSQL. Note that not all data types are available for table storage on each data source.

FedSQL Data Types
Data Type	Description
BIGINT	stores a large signed, exact whole number, with a precision of 19 digits. The range of integers is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Integer data types do not store decimal values; fractional portions are discarded.
BINARY(n)	stores fixed-length binary data, where n is the maximum number of bytes to store. The maximum number of bytes is required to store each value regardless of the actual size of the value.
CHAR(n)	stores a fixed-length character string, where n is the maximum number of characters to store. The maximum number of characters is required to store each value regardless of the actual size of the value. If `char(10)` is specified and the character string is only five characters long, the value is right-padded with spaces.
DATE	stores a calendar date. A date literal is specified in the format yyyy-mm-dd: a four-digit year (0001 to 9999), a two-digit month (01 to 12), and a two-digit day (01 to 31). For example, the date September 24, 1975 is specified as `1975-09-24`. FedSQL complies with ANSI SQL:1999 standards regarding dates. However, not all data sources support the full range of dates. For example, dates between 0001-01-01 and 1582-12-31 are not valid dates for a SAS data set and an SPD Engine data set.
DOUBLE	stores a signed, approximate, double-precision, floating-point number. Allows numbers of large magnitude and permits computations that require many digits of precision to the right of the decimal point.
DECIMAL\|NUMERIC(p,s)	stores a signed, exact, fixed-point decimal number, with user-specified precision and scale. The precision and scale determine the position of the decimal point. The precision is the maximum number of digits that can be stored to the left and right of the decimal point, with a range of 1 to 52. The scale is the maximum number of digits that can be stored following the decimal point. The scale must be less than or equal to the precision. For example, `decimal(9,2)` stores decimal numbers up to nine digits, with a two-digit, fixed-point fractional portion, such as 1234567.89. Note: The DECIMAL data type is supported for defining a column, inserting data into the column, and fetch operations. Other operations, such as using a DECIMAL column in an expression, result in the DECIMAL data type being converted to a DOUBLE.
FLOAT(p)	stores a signed, approximate, single-precision or double-precision, floating-point number. The user-specified precision determines whether the data type stores a single-precision or double-precision number. If the specified precision is equal to or greater than 25, the value is stored as a double-precision number, which is a DOUBLE. If the specified precision is less than 25, the value is stored as a single-precision number, which is a REAL. For example, `float(10)` specifies to store up to 10 digits, which results in a REAL data type.
INTEGER	stores a regular size signed, exact whole number, with a precision of 10 digits. The range of integers is -2,147,483,648 to 2,147,483,647. Integer data types do not store decimal values; fractional portions are discarded. Note: Integer division by zero does not produce the same result on all operating systems. It is recommended that you avoid integer division by zero.
NCHAR(n)	stores a fixed-length character string such as CHAR but uses a Unicode national character set, where n is the maximum number of multibyte characters to store. Depending on the platform, Unicode characters use either two or four bytes per character and support all international characters.
NVARCHAR(n)	stores a varying-length character string such as VARCHAR but uses a Unicode national character set, where n is the maximum number of multibyte characters to store. Depending on the platform, Unicode characters use either two or four bytes per character and can support all international characters.
REAL	stores a signed, approximate, single-precision, floating-point number.
SMALLINT	stores a small signed, exact whole number, with a precision of five digits. The range of integers is -32,768 to 32,767. Integer data types do not store decimal values; fractional portions are discarded.
TIME(p)	stores a time value. A time literal is specified in the format hh:mm:ss[.nnnnnnnnn]; a two-digit hour 00 to 23, a two-digit minute 00 to 59, and a two-digit second 00 to 61 (supports leap seconds), with an optional fraction value. For example, the time 6:30 a.m. is specified as `06:30:00`. When supported by a data source, the p parameter specifies the seconds precision. The seconds precision is an optional fraction value that is up to nine digits long.
TIMESTAMP(p)	stores both date and time values. A timestamp literal is specified in the format yyyy-mm-dd:hh:mm:ss[.nnnnnnnnn]: a four-digit year 0001 to 9999, a two-digit month 01 to 12, a two-digit day 01 to 31, a two-digit hour 00 to 23, a two-digit minute 00 to 59, and a two-digit second 00 to 61 (supports leap seconds), with an optional fraction value. For example, the date and time September 24, 1975 6:30 a.m. is specified as `1975-09-24:06:30:00`. When supported by a data source, the p parameter specifies the seconds precision. The seconds precision is an optional fraction value that is up to nine digits long.
TINYINT	stores a very small signed, exact whole number, with a precision of three digits. The range of integers is -128 to 127. Integer data types do not store decimal values; fractional portions are discarded.
VARBINARY(n)	stores varying-length binary data, where n is the maximum number of bytes to store. The maximum number of bytes is not required to store each value. If `varbinary(10)` is specified and the binary string uses only five bytes, only five bytes are stored in the column.
VARCHAR(n)	stores a varying-length character string, where n is the maximum number of characters to store. The maximum number of characters is not required to store each value. If `varchar(10)` is specified and the character string is only five characters long, only five characters are stored in the column.

When defining a data type, use the data type keywords for either the data types that are supported by FedSQL or the data types that are supported by the target database SQL language. That is,

If you submit FedSQL statements, use FedSQL data type keywords.
If you request an SQL pass-through and submit SQL statements using the SQL language that is implemented by the specific data source, use the data type names for the target database. For information about how to define data types using the SQL language for a specific data source, see the documentation for that data source.

Keep in mind that in order for data to be stored, the data type must be available for data storage in that data source. Although FedSQL supports several data types, the data types that can be defined for a particular table depend on the data source, because each data source does not necessarily support all FedSQL data types. In addition, data sources support variations of the standard SQL data types. That is, a specific data type that you specify might map to a different data type and might also have different attributes in the underlying data source. This occurs when a data source does not natively support a specific data type, but data values of a similar data type can be converted without data loss. For example, to support the INTEGER data type, a SAS data set maps the data type definition to SAS numeric, which is a DOUBLE.

For details about data source implementation for each data type, see Data Type Reference.

In addition, the CT_PRESERVE= connection argument, which controls how data types are mapped, can affect whether a data type can be defined. The values FORCE (default) and FORCE_COL_SIZE do not affect whether a data type can be defined. The values STRICT and SAFE can result in an error if the requested data type is not native to the data source or the specified precision or scale is not within the data source range. For information about the CT_PRESERVE= connection argument, see SAS Federation Server: Administrator’s Guide.