Built-In Data Types A built-in data type is a fundamental data type that the database server defines. A fundamental data type is atomic; that is, it cannot be broken into smaller pieces, and it serves as the building block for other data types. Figure 1-1 summarizes the built-in data types that Universal Server provides.
Figure 1-1 Built-In Data Types of Universal Server (1 of 3) Data Type Explanation BLOB Stores binary data in random-access chunks. BOOLEAN Stores the boolean values for true and false. BYTE Stores binary data in chunks that are not random access. CHAR(n) Stores single-byte or multibyte sequences of characters, including letters, numbers, and symbols of fixed length; collation is code-set dependent. CHARACTER(n) Is a synonym for CHAR. CHARACTER VARYING(m,r) Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols of varying length; collation is code-set dependent; is an ANSI-compliant version of the VARCHAR data type. CLOB Stores text data in random-access chunks. DATE Stores a calendar date. DATETIME Stores a calendar date combined with the time of day. DEC Is a synonym for DECIMAL. DECIMAL Stores numbers with definable scale and precision. DOUBLE PRECISION Behaves the same way as FLOAT. FLOAT(n) Stores double-precision floating-point numbers that correspond to the double data type in C (on most platforms). INT Is a synonym for INTEGER. INT8 Stores an 8-byte integer value. These whole numbers can be in the range -9,223,372,036,854,775,87 to9,223,372,036,854,775,807 (which is -(263-1) to 263-1). INTEGER Stores whole numbers from - 2,147,483,647 to +2,147,483,647 (which is -(231-1) to 231-1). INTERVAL Stores a span of time. LVARCHAR Stores single-byte or multibyte strings of letters, numbers, and symbols of varying length to a maximum of 32 kilobytes; is also the external storage format for opaque data types; collation is code-set dependent. MONEY(p,s) Stores a currency amount. NCHAR(n) Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols; collation is locale dependent. For more information, see the Guide to GLS Functionality. NUMERIC(p,s) Is a synonym for DECIMAL. NVARCHAR(m,r) Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols of varying length to a maximum of 255 bytes; collation is locale dependent. For more information, see the Guide to GLS Functionality. REAL Is a synonym for SMALLFLOAT. SERIAL Stores sequential integers; has the same range of values as INTEGER. SERIAL8 Stores large sequential integers; has the same range of values as INT8. SMALLFLOAT Stores single-precision floating-point numbers that correspond to the float data type in C (on most platforms). SMALLINT Stores whole numbers from - 32,767 to +32,767 (which is -(215-1) to 215-1). TEXT Stores text data in chunks that are not random access. VARCHAR(m,r) Stores single-byte or multibyte strings of letters, numbers, and symbols of varying length to a maximum of 255 bytes; collation is code-set dependent. For more information on these built-in data types, see their entries in the chapter on data types in the Informix Guide to SQL: Reference.
(1 of 3)
BLOB
Stores binary data in random-access chunks.
BOOLEAN
Stores the boolean values for true and false.
BYTE
Stores binary data in chunks that are not random access.
CHAR(n)
Stores single-byte or multibyte sequences of characters, including letters, numbers, and symbols of fixed length; collation is code-set dependent.
CHARACTER(n)
Is a synonym for CHAR.
CHARACTER VARYING(m,r)
Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols of varying length; collation is code-set dependent; is an ANSI-compliant version of the VARCHAR data type.
CLOB
Stores text data in random-access chunks.
DATE
Stores a calendar date.
DATETIME
Stores a calendar date combined with the time of day.
DEC
Is a synonym for DECIMAL.
DECIMAL
Stores numbers with definable scale and precision.
DOUBLE PRECISION
Behaves the same way as FLOAT.
FLOAT(n)
Stores double-precision floating-point numbers that correspond to the double data type in C (on most platforms).
INT
Is a synonym for INTEGER.
INT8
Stores an 8-byte integer value. These whole numbers can be in the range -9,223,372,036,854,775,87 to9,223,372,036,854,775,807 (which is -(263-1) to 263-1).
INTEGER
Stores whole numbers from - 2,147,483,647 to +2,147,483,647 (which is -(231-1) to 231-1).
INTERVAL
Stores a span of time.
LVARCHAR
Stores single-byte or multibyte strings of letters, numbers, and symbols of varying length to a maximum of 32 kilobytes; is also the external storage format for opaque data types; collation is code-set dependent.
MONEY(p,s)
Stores a currency amount.
NCHAR(n)
Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols; collation is locale dependent. For more information, see the Guide to GLS Functionality.
NUMERIC(p,s)
NVARCHAR(m,r)
Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols of varying length to a maximum of 255 bytes; collation is locale dependent. For more information, see the Guide to GLS Functionality.
REAL
Is a synonym for SMALLFLOAT.
SERIAL
Stores sequential integers; has the same range of values as INTEGER.
SERIAL8
Stores large sequential integers; has the same range of values as INT8.
SMALLFLOAT
Stores single-precision floating-point numbers that correspond to the float data type in C (on most platforms).
SMALLINT
Stores whole numbers from - 32,767 to +32,767 (which is -(215-1) to 215-1).
TEXT
Stores text data in chunks that are not random access.
VARCHAR(m,r)
Stores single-byte or multibyte strings of letters, numbers, and symbols of varying length to a maximum of 255 bytes; collation is code-set dependent.
Complex Data Types A complex data type is built from a combination of other data types with an SQL type constructor. An SQL statement can access individual components within the complex type. There are two kinds of complex types:
Figure 1-2 Complex Data Types of Universal Server Data Type Explanation LIST(e) Stores a collection of values that have an implicit position (first, second, and so on), and allows duplicate values. All elements have the same element type, e. MULTISET(e) Stores a collection of values that have no implicit position, and allows duplicate values. All elements have the same element type, e. Named row type A row type created with the CREATE ROW TYPE statement that has a defined name and inheritance properties and can be used to construct a typed table. A named row type is not equivalent to another named row type, even if its field definitions are the same. ROW (Unnamed row type) A row type created with the ROW constructor that has no defined name and no inheritance properties. Two unnamed row types are equivalent if they have the same number of fields and if corresponding fields have the same data type, even if the fields have different names. SET(e) Stores a collection of values that have no implicit position, and does not allow duplicate values. All elements have the same element type, e. For more information on these complex data types, see the chapter on data types in the Informix Guide to SQL: Reference.
LIST(e)
Stores a collection of values that have an implicit position (first, second, and so on), and allows duplicate values. All elements have the same element type, e.
MULTISET(e)
Stores a collection of values that have no implicit position, and allows duplicate values. All elements have the same element type, e.
Named row type
A row type created with the CREATE ROW TYPE statement that has a defined name and inheritance properties and can be used to construct a typed table. A named row type is not equivalent to another named row type, even if its field definitions are the same.
ROW
(Unnamed row type)
A row type created with the ROW constructor that has no defined name and no inheritance properties. Two unnamed row types are equivalent if they have the same number of fields and if corresponding fields have the same data type, even if the fields have different names.
SET(e)
Stores a collection of values that have no implicit position, and does not allow duplicate values. All elements have the same element type, e.
Operations A data type tells the database server which operations it can perform on the data type values. Universal Server provides the following types of operations on data types:
Operator Classes A data type tells the database server which operator class to associate with the data type values when they are stored in a secondary access method. The secondary access method builds and accesses an index. An operator class associates a group of operators with a secondary access method. When you extend an operator class, you provide additional functions that can be used as filters in queries and for which the database server can use an index. Universal Server provides an operator class for the built-in secondary access method, a generic B-tree. This operator class handles the data types that the database server provides. For a description of operator classes and how to extend them, see Chapter 4, "Extending an Operator Class."
Casts A data type tells the database server which cast to use to convert the data type value to a different data type. A cast performs the necessary operations for conversion from the data type to another data type. When two data types have different internal formats, the database server calls a casting function to convert one data type to another. Universal Server provides casts between the built-in data types. For a description of casts and how to extend them, see Chapter 3, "Creating User-Defined Casts."
How Can You Extend the Data Type System? Would be nice to add a marketing-level summary/description of the Universal Server data-type system. So far, no response from Kathy Mori. The data type system of Universal Server is an extensible data type system. That is, this data type system is flexible enough to support the following kinds of changes:
