## SQLWays - Pervasive.SQL Data Types

This chapter describes the data types supported by Pervasive.SQL.

- CHAR and VARCHAR
- BIGINT, UBIGINT, INTEGER, UINTEGER, SMALLINT, USMALLINT, TINYINT and UTINYINT
- DATE, TIME and TIMESTAMP
- DECIMAL, NUMERIC, NUMERICSA and NUMERICSTS
- MONEY and CURRENCY
- FLOAT, REAL, DOUBLE, BFLOAT4 and BFLOAT8
- LONGVARCHAR
- BINARY and LONGVARBINARY
- BIT, IDENTITY and SMALLIDENTITY

### CHAR and VARCHAR

CHAR(n)

Fixed-length character data with length of n characters. n must be a value from 1 through 255. CHAR columns are padded with blanks. You must specify the size for CHAR columns.

VARCHAR(n)

Variable-length character data with length of n characters. n must be a value from 1 through 254. VARCHAR columns are not padded with blanks. You must specify the size for VARCHAR columns.

### BIGINT, UBIGINT, INTEGER, UINTEGER, SMALLINT, USMALLINT, TINYINT and UTINYINT

BIGINT

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

UBIGINT

Unsigned integer (whole number) data from 0 through 18446744073709551615. Storage size is 8 bytes.

INTEGER

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes.

UINTEGER

Unsigned integer (whole number) data from 0 through 2^32 (4294967295) Storage size is 4 bytes.

SMALLINT

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

USMALLINT

Unsigned integer data from 0 through 2^16 (65535). Storage size is 2 bytes.

TINYINT

Integer data from -2^7 (-128) through 2^7 - 1 (127). Storage size is 1 byte.

UTINYINT

Unsigned integer data from 0 through 255. Storage size is 1 byte.

### DATE, TIME and TIMESTAMP

Date and time data types for representing date and time of day.

DATE

The DATE data type stores date information (year, month, day) from 01-01-0001 through December 31, 9999. The DATE data type is stored internally as a 4-byte value.

TIME

The TIME data type stores time information (hour, minute, second and hundreds of a seconds) from 00:00:00 through 23:59:59.

The TIME data type is stored internally as a 4-byte value.

TIMESTAMP

The TIMESTAMP data type stores day and time information (year, month, day, hour, minute, second and fractional seconds) from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 UTC.

The TIMESTAMP data type is stored internally as a 8-byte unsigned value representing septaseconds (10^-7 second) since January 1, 0001 in a Gregorian calendar, Coordinated Universal Time (UTC).

### DECIMAL, NUMERIC, NUMERICSA and NUMERICSTS

Numeric data types with fixed precision and scale.

DECIMAL(p,s)

p (precision) specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through 64.

s (scale) specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p.

Precision and scale must be specified.

The DECIMAL data type is stored internally as a packed decimal number with two decimal digits per byte.

NUMERIC(p,s)

The NUMERIC data type is similar to DECIMAL. The maximum precision is 15.

NUMERIC values are stored as ASCII strings, right justified with leading zeros. Each digit occupies one byte internally. The embedded sign has an EBCDIC value.

NUMERICSA(p,s)

The NUMERICSA data type (sometimes called NUMERIC SIGNED ASCII) is a COBOL data type that is the same as the NUMERIC data type, except that the embedded sign has an ASCII value instead of an EBCDIC value.

The maximum precision is 15.

NUMERICSTS(p,s)

The NUMERICSTS data type (sometimes called SIGN TRAILING SEPARATE) is a COBOL data type that is the same as the NUMERIC data type, except that the sign is saved in a separate byte.

The maximum precision is 14.

### MONEY and CURRENCY

MONEY

The MONEY data type has the same internal representation as the DECIMAL type, with an implied two decimal places.

MONEY is a legacy data type and Pervasive Software recommends to use the DECIMAL data type instead of MONEY.

CURRENCY

The CURRENCY data type has an implied four digit scale of decimal places, which represents the fractional component of the currency data value.

### FLOAT, REAL, DOUBLE, BFLOAT4 and BFLOAT8

FLOAT

The FLOAT data type is consistent with the IEEE standard for single and double-precision real numbers.

REAL

The REAL data type is defined as a 4-byte FLOAT.

DOUBLE

The DOUBLE data type is defined as a 8-byte FLOAT.

BFLOAT4

The BFLOAT4 data type is a single precision real number.

The BFLOAT4 type is commonly used in legacy BASIC applications. Microsoft refers to this data type as MBF (Microsoft Binary Format), and no longer supports this type in the Visual Basic environment. Pervasive Software recommends to use the REAL data type instead of BFLOAT4.

BFLOAT8

The BFLOAT8 data type is a double precision real number.

The BFLOAT8 type is commonly used in legacy BASIC applications. Microsoft refers to this data type as MBF (Microsoft Binary Format), and no longer supports this type in the Visual Basic environment. Pervasive Software recommends to use the DOUBLE data type instead of BFLOAT4.

### LONGVARCHAR

LONGVARCHAR

Variable-length character data with a maximum length of 2^31 - 1 (2,147,483,647) characters. Storage size is the actual length in bytes of the data entered + 8-byte header.

### BINARY and LONGVARBINARY

Binary data types.

BINARY(n)

Fixed-length binary data of n bytes. n must be a value from 1 through 255. BINARY columns are padded with binary zeros. You must specify the size for BINARY columns.

LONGVARBINARY

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes. Storage size is the actual length in bytes of the data entered + 8-byte header.

### BIT, IDENTITY and SMALLIDENTITY

BIT

Integer data type that can be 1 or 0. Integer values other than 0 or 1 are accepted, but are always interpreted as 1. Columns with a datatype of BIT cannot be NULL.

If some BIT columns follow each other, they are saved in one byte, otherwise each BIT is stored in one byte.

IDENTITY

The IDENTITY data type is stored as a signed 4-byte INTEGER. IDENTITY is used to automatically assign the next highest value when a record is inserted into a table.

When an INSERT operation occurs for a record with ASCII zero in the IDENTITY field, the database engine assigns the next highest value.

SMALLIDENTITY

The SMALLIDENTITY data type is the same as the IDENTITY data type, except that a signed 2-byte integer (SMALLINT) is used to store SMALLIDENTITY values.

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.