Adaptive Server Enterprise data type equivalents

int

INT,BIGINT,UNSIGNED INT, UNSIGNED BIGINT, or NUMERIC

Sybase IQ does not allow scaled integers, such as INT(7,3). Data in the form INT(precision,scale) is converted to NUMERIC(precision,scale). This differs from Sybase IQ versions prior to 12.0, and from Adaptive Server Enterprise, in which int data types can be values between -2,147,483,648 and 2,147,483,647, inclusive.

To handle larger integer values, you can use a BIGINT, an unsigned integer (UNSIGNED INT), or an UNSIGNED BIGINT data type. With UNSIGNED INT, the last bit is used as part of the value. There is no positive or negative indication; all numbers are assumed to be positive, so the value can go up to 4,294,967,295.

numeric

DECIMAL or NUMERIC with appropriate precision

If the precision of the Sybase IQ data type you define is too small to store the Adaptive Server Enterprise value, the value converts to NULL.

decimal

DECIMAL or NUMERIC with appropriate precision

See above.

smallint

SMALLINT or NUMERIC

Sybase IQ SMALLINTdoes not allow precision and scale. Adaptive Server Enterprise smallint(precision,scale) is converted to NUMERIC(precision,scale)See INT above.

tinyint

TINYINT

Sybase IQ TINYINT columns do not allow precision and scale. Adaptive Server Enterprise tinyint(precision,scale) is converted to NUMERIC(precision,scale). See INT above.

bit

BIT

unsigned smallint

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type unsigned smallint, but you can insert data from an ASE database column of data type unsigned smallint using INSERT...LOCATION.

float (precision)

FLOAT (precision)

IQ supports greater precision for FLOAT

HNG indexes do not allow FLOAT, REAL, or DOUBLE data.

double precision

DOUBLE

real

REAL

char

CHAR

Sybase IQ and Adaptive Server Enterprise character (char or CHAR) data types are the same except that Sybase IQ can handle NULLs. If you want an Sybase IQ CHAR column to exactly match an Adaptive Server Enterprise char column, specify Sybase IQ column as NOT NULL. Sybase IQ default allows NULLs. Adaptive Server Enterprise char columns that allow NULLs are internally converted to varchar.

varchar

VARCHAR

See char notes above.

nchar

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type nchar, but you can insert data from an ASE database column of data type nchar using INSERT...LOCATION.

nvarchar

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type nvarchar, but you can insert data from an ASE database column of data type nvarchar using INSERT...LOCATION.

text

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type text, but you can insert data from an ASE database column of data type text using INSERT...LOCATION.

unichar

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type unichar, but you can insert data from an ASE database column of data type unichar using INSERT...LOCATION.

univarchar

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type univarchar, but you can insert data from an ASE database column of data type univarchar using INSERT...LOCATION.

unitext

Not supported

Sybase IQ does not support the Adaptive Server Enterprise data type unitext, but you can insert data from an ASE database column of data type unitext using INSERT...LOCATION.

money

NUMERIC(19,4)

money data is converted implicitly to NUMERIC(19,4).

smallmoney

NUMERIC(10,4)

datetime

TIMESTAMP or DATE or TIME

Adaptive Server Enterprise datetime columns maintain date and time of day values in 4 bytes for number of days before or after base date of virtual date 0/0/0000 and 8 bytes for time of day, accurate to within one 1,000,000th of a second. Sybase IQ TIMESTAMP (or DATETIME) columns maintain date and time of day values in two 4-byte integers: 4 bytes for number of days since 1/1/0 and 4 bytes for time of day, based on 24-hour clock, accurate to within one 10,000th of a second. Sybase IQ automatically handles the conversion.

Sybase IQ also has a separate DATE data type, a single 4-byte integer. If you want to extract just a date from a SQL Server or Adaptive Server Enterprise datetime column, you can do this with Sybase IQ DATE data type. To do this, define an Sybase IQ DATE column with same name as the Adaptive Server Enterprise datetime column. Sybase IQ automatically picks up appropriate portion of datetime value.

smalldatetime

TIMESTAMP or DATETIME or DATE or TIME

Define Adaptive Server Enterprise smalldatetime columns as TIMESTAMP (or DATETIME) data type in Sybase IQ. Sybase IQ properly handles the conversion. As with regular datetime, if you want to extract just a date from an Adaptive Server Enterprise smalldatetime column, do it with the Sybase IQ DATE data type.

date

date

You can insert data from an ASE database column of data type date using INSERT...LOCATION.

time

time

The Sybase IQ data type is the Time of day, containing hour, minute, second, and fraction of a second. The fraction is stored to 6 decimal places. A time value requires 8 bytes of storage.

The Adaptive Server Enterprise data type time is between 00:00:00:000 and 23:59:59:999. You can use either military time or 12AM for noon and 12PM for midnight. A time value must contain either a colon or the AM or PM signifier. AM or PM may be in either uppercase or lowercase. A time value requires 4 bytes of storage.

You can insert data from an ASE database column of data type time using INSERT...LOCATION.

binary

BINARY

Sybase IQ pads trailing zeros on all BINARY columns. Always create BINARY columns with an even number of characters for length.

HNG indexes do not allow BINARY data.

varbinary

VARBINARY

Sybase IQ does not pad or truncate trailing zeros on VARBINARY columns. Always create VARBINARY columns with an even number of characters for length.

HNG indexes do not allow VARBINARY data.

If you use INSERT ... LOCATION to insert data selected from a VARBINARY column, set the LOAD_MEMORY_MB option on the local database to limit memory used by the insert, and set ASE_BINARY_DISPLAY to ‘OFF’ on the remote database.