SQL Data Types

Every data value belongs to some SQL <data type>. The logical representation of a data value is known as a <literal>. SQL supports three sorts of <data type>s -- predefined <data type>s, constructed <data type>s, and <user-defined data type>s, or UDTs -- all of which may be used to define a set of valid data values. The predefined <data type>s are all scalar types; they contain atomic values (i.e., values that are not composed of sets of values of other <data type>s). The constructed <data type>s are mostly composite types; they contain array values (i.e., values that are composed of sets of values, each of a declared predefined <data type>). The UDTs are composite types. Their values and attributes are totally user-defined.

Each host language supported by the SQL Standard has its own data types. These are distinct from SQL <data type>s, though they often have similar names. The Standard includes instructions on how to map SQL <data type>s to host language data types.

SQL-data values are either non-null values or the null value. The null value is a special implementation-dependent value that can be assigned to any SQL <data type>. It is used to represent "value unknown" or "value inapplicable" and is distinct from all non-null values. The null value is often denoted by the <keyword> NULL.

Predefined <data type>s

SQL's predefined scalar <data type>s are identified by these <keyword>s: INTEGER, SMALLINT, NUMERIC, DECIMAL, FLOAT, REAL, DOUBLE PRECISION, BIT, BIT VARYING, BINARY LARGE OBJECT, CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, CHARACTER LARGE OBJECT, NATIONAL CHARACTER LARGE OBJECT, DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL, and BOOLEAN.

Number <data type>s

A numeric value is either an exact numeric (integer or decimal) number or an approximate numeric (floating point) number. The numeric <data type>s INTEGER (or INT), SMALLINT, NUMERIC, DECIMAL (or DEC), FLOAT, REAL, and DOUBLE PRECISION store numbers inserted in either exact numeric form (e.g., 75, -6.2) or approximate numeric form (e.g., 1.256E-4, -1.03E+5). INT and SMALLINT are exact numeric types with a predefined precision and a scale of zero; NUMERIC and DECIMAL are exact numeric types with definable precisions and scales; FLOAT is an approximate numeric type with a definable precision; and REAL and DOUBLE PRECISION are approximate numeric types with predefined precisions. All numbers are mutually assignable and mutually comparable. Assignment and comparison are performed in the familiar, algebraic manner. The following SQL operations involve numbers: addition and unary plus, subtraction and unary minus, multiplication, division, assignment, comparison, ABS, BETWEEN, BIT_LENGTH, CARDINALITY, CHAR_LENGTH, DISTINCT, EXISTS, EXTRACT, FOR ALL, FOR SOME, IN, IS NULL, MATCH, MOD, OCTET_LENGTH, POSITION, and UNIQUE.

Bit String <data types>s

A bit string value is any sequence of bits or hexits. The bit string <data type>s BIT and BIT VARYING store bit string values inserted in either binary form (any sequence of zero or more 0- bits or 1-bits) or hexadecimal form (any sequence of zero or more 0-hexits, 1-hexits, 2-hexits, 3-hexits, 4-hexits, 5-hexits, 6- hexits, 7-hexits, 8-hexits, 9-hexits, A-hexits, B-hexits, C- hexits, D-hexits, E-hexits, or F-hexits). BIT has a definable fixed length; BIT VARYING has a definable variable length. All bit strings are mutually assignable and mutually comparable. Assignment of a bit string is performed bit-by-bit beginning with the source's most significant bit. For comparison purposes, a 0-bit is less than a 1-bit. The following SQL operations involve bit strings: concatenation, assignment, comparison, BETWEEN, BIT_LENGTH, CHAR_LENGTH, DISTINCT, EXISTS, FOR ALL, FOR SOME, IN, IS NULL, MATCH, OCTET_LENGTH, POSITION, SUBSTRING, and UNIQUE.

Binary String <data types>s

A binary string value is any sequence of octets that aren't associated with a Character set. The binary string <data type> BINARY LARGE OBJECT (BLOB) stores binary string values inserted in hexadecimal form. BLOB has a definable variable length. All binary strings are mutually assignable and mutually comparable. Assignment of a binary string is performed octet-by-octet beginning with the source's most significant octet. Comparison is supported only for equality. The following SQL operations involve binary strings: concatenation, assignment, comparison, BIT_LENGTH, CHAR_LENGTH, EXISTS, FOR ALL, FOR SOME, IS NULL, LIKE, OCTET_LENGTH, OVERLAY, POSITION, SUBSTRING, and TRIM.

Character String <data type>s

A character string value is any sequence of characters that belong to a given Character set. The character string <data type>s CHARACTER (CHAR), CHARACTER VARYING (VARCHAR), NATIONAL CHARACTER (NCHAR) and NATIONAL CHARACTER VARYING (NCHAR VARYING) store character strings, while the character string <data type>s CHARACTER LARGE OBJECT (CLOB) and NATIONAL CHARACTER LARGE OBJECT (NCLOB) store large object character strings. CHAR and NCHAR have a definable fixed length; VARCHAR, NCHAR VARYING, CLOB and NCLOB have a definable variable length. CHAR, VARCHAR, and CLOB have a definable Character set; NCHAR, NCHAR VARYING, and NCLOB have a predefined Character set. All of the character string <data type>s have a definable Collation or collating sequence. All character strings that belong to the same Character set are mutually assignable and mutually comparable if they have the same Collation. Assignment of a character string is performed character-by-character beginning with the source's first character. The result of a character string comparison is determined by the rules of the Collation used for the comparison. The following SQL operations involve character strings: concatenation, assignment, comparison, BETWEEN, BIT_LENGTH, CHAR_LENGTH, CONVERT, DISTINCT, EXISTS, FOR ALL, FOR SOME, IN, IS NULL, LIKE, LOWER, MATCH, OCTET_LENGTH, OVERLAY, POSITION, SIMILAR, SUBSTRING, TRANSLATE, TRIM, UNIQUE, and UPPER.

Temporal <data type>s

A temporal value is a date, a time, a timestamp, or an interval of time. The temporal <data type> DATE stores dates, TIME and TIME WITH TIME ZONE store times, TIMESTAMP and TIMESTAMP WITH TIME ZONE store timestamps, and INTERVAL stores intervals. DATE has a predefined precision; TIME, TIME WITH TIME ZONE, TIMESTAMP and TIMESTAMP WITH TIME ZONE have definable fractional seconds precisions. There are two classes of INTERVAL. The first, year-month intervals, has a definable precision that includes some contiguous combination of the YEAR and MONTH datetime fields. The second, day-time intervals, has a definable precision that includes some contiguous combination of the DAY, HOUR, MINUTE, and SECOND datetime fields. TIME WITH TIME ZONE values are (and TIMESTAMP WITH TIME ZONE values include) times that are maintained in Universal Coordinated Time (UTC) -- with a portion of the value representing a time zone offset. The time zone offset is an interval that specifies the difference between UTC and the actual date and time in the value's time zone. All temporal values of the same type are mutually assignable and mutually comparable; the results must follow the usual rules for temporal values according to the Gregorian calendar and the 24-hour clock. The following SQL operations involve temporal values: addition, subtraction, multiplication, division, assignment, comparison, ABS, BETWEEN, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DISTINCT, EXISTS, EXTRACT, FOR ALL, FOR SOME, IN, IS NULL, LOCALTIME, LOCALTIMESTAMP, MATCH, OVERLAPS, and UNIQUE.

Boolean <data type>s

A boolean value is a truth value; either TRUE, FALSE, or UNKNOWN. (The truth value UNKNOWN is sometimes represented by the null value.) The boolean <data type> BOOLEAN stores truth values. All truth values are mutually assignable and mutually comparable. TRUE and FALSE may be assigned to any boolean target; UNKNOWN may only be assigned if the boolean target allows NULLs. For comparison purposes, TRUE is greater than FALSE. The following SQL operations involve boolean values: AND, IS, NOT, OR, and the results of any predicate or search condition.

Constructed <data types>:

An SQL constructed <data type> is either a <reference type>, a <row type>, or a <collection type>. A <reference type> is a scalar constructed <data type> identified by the <keyword> REF. A <row type> is a composite constructed <data type> identified by the <keyword> ROW. A <collection type> is a composite constructed <data type>, identified by the <keyword> ARRAY.

<reference type>s

A reference value points to some row of a referenceable Base table (that is, a Base table that has a "with REF value" property).

<row type>s

A <row type> is a sequence of one or more (Field, <data type>) pairs. A value of a <row type> consists of one value for each of its Fields.

<collection type>s

A <collection type> is a composite data value that consists of zero or more elements of a specified <data type>, known as the element type -- that is, in SQL3, a <collection type> is an array.

User-defined Types

The SQL user-defined types (UDTs) are Schema Objects that can be defined by a standard, by a DBMS, or by an SQL application. UDTs have no corresponding <literal>s.

Data Type Conversions

SQL allows for implicit <data type> conversion in expressions and in FETCH, SELECT, INSERT, DELETE, and UPDATE operations. Explicit <data type> conversions may be performed with the CAST operator.

Sites

As defined in the SQL Standard, a site is "a place that holds an instance of a value of a specified <data type>". A site has a defined degree of persistence -- if it exists until deliberately destroyed, it is a persistent site; if it ceases to exist at the end of an SQL statement, SQL transaction, or SQL-session, it is a temporary site; if it exists only to hold an argument or returned value, it is a transient site. The principal kind of persistent or temporary site is a Base table. Some sites may be referenced by their names (e.g., Base tables and SQL variables) or by a REF value. A site occupied by an element of an array may be referenced by its element number.

The instance at a site can be changed in two ways: by assignment or by mutation. Assignment is an operation that replaces the value at a site (the "target") with a new value (the "source"). Mutation is an operation that changes the value of some attribute of an instance at a site whose <data type> is a UDT. Neither assignment nor mutation has any effect on the reference value of a site, if any.

Every site has a nullability characteristic, which indicates whether it may contain the null value (is "possibly nullable") or not (is "known not nullable"). Only the Columns of Base tables may be constrained to be "known not nullable", but the characteristic is inheritable.

Locators

An embedded host language variable, host parameter, SQL parameter, or external routine, or the value returned by an external function may all be specified to be a Locator. The purpose of a Locator is to allow very large data values to be operated on without transferring the entire value to and from your application program.

A Locator is not SQL-data; instead, it is an SQL-session Object that can be used to reference a specific value. The SQL Standard defines three types of Locators: the large object (LOB) Locator, the UDT Locator and the array Locator. A LOB Locator is either (a) a BLOB Locator (its value identifies a binary large object), (b) a CLOB Locator (its value identifies a character large object) or (c) an NCLOB Locator (its value identifies a national character large object). A UDT Locator identifies a value of a given user-defined type. An array Locator identifies a value of a given array.

Note:

Comments

Comments loading...