2.3 SQL2003 and Platform-Specific Datatypes

     

A table can contain one or many columns . Each column must be defined with a datatype that provides a general classification of the data that the column will store. In real-world applications, datatypes provide some control and efficiency as to how tables are defined and how the data is stored within the table. Using specific datatypes enables better, more understandable queries and helps control the integrity of data.

The tricky thing about SQL2003 datatypes is that they do not always map directly to an identical implementation in a given platform. Although the platforms specify "datatypes" that correspond to the SQL2003 datatypes, these are not always true SQL2003 datatypes. For example, MySQL's implementation of a BIT datatype is actually identical to a CHAR(1) datatype value. Nonetheless, each of the platform datatypes is close enough to the standard to be both easily understandable and job-ready.

The official SQL2003 datatypes (as opposed to platform-specific datatypes) fall into the general categories described in Table 2-8. (Note that the SQL2003 standard contains a few rarely used datatypes ( ARRAY , MULTISET , REF , and ROW ) that are shown only in Table 2-8 and not elsewhere in the book.)

Table 2-8. SQL2003 categories and datatypes

Category

Example datatypes and abbreviations

Description

BINARY

BINARY LARGE OBJECT (BLOB)

This datatype stores binary string values in hexadecimal format. Binary string values are stored without reference to any character set and without any length limit.

BOOLEAN

BOOLEAN

This datatype stores truth values ”either TRUE or FALSE .

CHARACTER string types

CHAR

CHARACTER VARYING (VARCHAR)

These datatypes can store any combination of characters from the applicable character set. The varying datatypes allow variable lengths, while the other datatypes allow only fixed lengths. Also, the variable-length datatypes automatically trim trailing spaces, while the other datatypes pad all open space.

 

NATIONAL CHARACTER (NCHAR)

NATIONAL CHARACTER VARYING (NCHAR VARYING)

The national character datatypes are designed to support a particular implementation-defined character set.

 

CHARACTER LARGE OBJECT (CLOB)

CHARACTER LARGE OBJECT and BINARY LARGE OBJECT are collectively referred to as large object string types .

 

NATIONAL CHARACTER LARGE OBJECT (NCLOB)

Same as CHARACTER LARGE OBJECT, but supports a particular implementation-defined character set.

DATALINK

DATALINK

Defines a reference to a file or other external data source that is not part of the SQL environment.

INTERVAL

INTERVAL

Specifies a set of time values or span of time.

COLLECTION

ARRAY

MULTISET

ARRAY was offered in SQL99. MULTISET was added in SQL2003. Whereas an ARRAY is a set-length, ordered collection of elements, MULTISET is a variable-length, unordered collection of elements. The elements ARRAY and MULTISET must be of a predefined datatype.

NUMERIC

INTEGER

(INT)

SMALLINT

BIGINT

NUMERIC(p,s)

DEC[IMAL](p,s)

FLOAT(p,s)

REAL

DOUBLE PRECISION

These datatypes store exact numeric values (integers or decimals) or approximate (floating point) values. INT , BIGINT , and SMALLINT store exact numeric values with a predefined precision and a scale of zero. NUMERIC and DEC store exact numeric values with a definable precision and a definable scale. FLOAT stores approximate numeric values with a definable precision, while REAL and DOUBLE PRECISION have predefined precisions. You may define a precision (p) and scale (s) for a DECIMAL , FLOAT , or NUMERIC datatype to indicate the total number of allowed digits in the number and the number of decimal places, respectively.

INTEGER (INT) , SMALLINT , and DEC[IMAL] (p,s) are sometimes referred to as exact numeric types , while FLOAT (p,s), REAL , and DOUBLE PRECISION are sometimes called approximate numeric types .

TEMPORAL

DATE

TIME

TIME WITH TIME ZONE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

These datatypes handle values related to time. DATE and TIME are self-explanatory. Datatypes with the WITH TIME ZONE suffix also include a time zone offset. The TIMESTAMP datatypes store a value that represents the a precise moment in time. Temporal types are also known as datetime types .

XML

XML

Stores XML data and can be used wherever a SQL datatype is allowed, such as a column of a table, field in a row, etc. Operations on the values of an XML type assume a tree-based internal data structure. The internal data structure is based on the XML Information Set Recommendation (Infoset) using a new document information item called the XML root information item.


Not every database platform supports each and every ANSI SQL datatype. Table 2-9 compares datatypes across the five platforms. The table is organized by datatype name. Be careful to look for footnotes when reading this table because some platforms support a datatype of a given name , but implement it in a different or contrary way than the ANSI standard and/or other vendors .

While platforms may support similarly named datatypes, their implementations can vary. Please consult the detailed information in this chapter for specific requirements of each platform's datatypes.


Table 2-9. Comparison of platform-specific datatypes

Vendor datatype

DB2

MySQL

Oracle

PostgreSQL

SQL Server

SQL2003 datatype

BFILE

   

Y

   

none

BIGINT

Y

Y

   

Y

BIGINT

BINARY

       

Y

BLOB

BINARY_FLOAT

   

Y

   

FLOAT

BINARY_DOUBLE

   

Y

   

DOUBLE PRECISION

BIT

 

Y

 

Y

Y

none

BIT VARYING, VARBIT

     

Y

 

none

BLOB

Y

Y

Y

   

BLOB

BOOL, BOOLEAN

 

Y

 

Y

 

BOOLEAN

BOX

     

Y

 

none

BYTEA

     

Y

 

BLOB

CHAR, CHARACTER

Y

Y

Y

Y

Y

CHARACTER

CHAR FOR BIT DATA

Y

       

none

CIDR

     

Y

 

none

CIRCLE

     

Y

 

none

CLOB

Y

 

Y

   

CLOB

CURSOR

       

Y

none

DATALINK

Y

       

DATALINK

DATE

Y

Y

Y

Y

 

DATE

DATETIME

 

Y

   

Y

TIMESTAMP

DBCLOB

Y

       

NCLOB

DEC, DECIMAL

Y

Y

Y

Y

Y

DECIMAL

DOUBLE, DOUBLE PRECISION

Y

Y

Y

Y [1]

Y

FLOAT

ENUM

 

Y

     

none

FLOAT

Y

Y

Y

 

Y

DOUBLE PRECISION

FLOAT4

     

Y [2]

 

FLOAT (P)

FLOAT8

     

Y [3]

 

FLOAT (P)

GRAPHIC

Y

       

BLOB

IMAGE

       

Y

none

INET

     

Y

 

none

INT, INTEGER

Y

Y

Y

Y

Y

INTEGER

INT2

     

Y

 

SMALLINT

INT4

     

Y

 

INT, INTEGER

INTERVAL

     

Y

 

INTERVAL

INTERVAL DAY TO SECOND

   

Y

   

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

   

Y

   

INTERVAL YEAR TO MONTH

LINE

     

Y

 

none

LONG

   

Y

   

none

LONG VARCHAR

Y

       

none

LONGBLOB

 

Y

     

BINARY LARGE OBJECT

LONG RAW

   

Y

   

BLOB

LONG VARGRAPHIC

Y

       

none

LONGTEXT

 

Y

     

none

LSEG

     

Y

 

none

MACADDR

     

Y

 

none

MEDIUMTEXT

 

Y

     

none

MEDIUMBLOB

 

Y

     

none

MONEY

     

Y [4]

Y

none

NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NCHAR VARYING,NVARCHAR

 

Y

Y

 

Y

NATIONAL CHARACTER VARYING

NCHAR, NATIONAL CHAR, NATIONAL CHARACTER

   

Y

 

Y

NATIONAL CHARACTER

NCLOB

   

Y

   

NCLOB

NTEXT, NATIONAL TEXT

       

Y

NCLOB

NVARCHAR2(N)

   

Y

   

none

NUMBER

Y

Y

Y

Y [5]

Y

none

NUMERIC

         

NUMERIC

OID

     

Y

 

none

PATH

     

Y

 

none

POINT

     

Y

 

none

POLYGON

     

Y

 

none

RAW

   

Y

   

none

REAL

Y

Y

Y

 

Y

REAL

ROWID

   

Y

   

none

ROWVERSION

       

Y

none

SERIAL, SERIAL4

     

Y

 

none

SERIAL8, BIGSERIAL

     

Y

 

none

SET

 

Y

     

none

SMALLDATETIME

       

Y

none

SMALLINT

Y

Y

Y

 

Y

SMALLINT

SMALLMONEY

       

Y

none

SQL_VARIANT

       

Y

none

TABLE

       

Y

none

TEXT

 

Y

 

Y

Y

none

TIME

Y

   

Y

 

TIME

TIMESPAN

     

Y

 

INTERVAL

TIMESTAMP

Y

 

Y

Y

Y

TIMESTAMP

TIMETZ

     

Y

 

TIME WITH TIME ZONE

TINYINT

       

Y

none

UNIQUEIDENTIFIER

       

Y

none

UROWID

   

Y

   

none

VARBINARY

       

Y

BLOB

VARCHAR, CHAR VARYING ,

CHARACTER VARYING

Y

Y

Y [6]

Y

Y

CHARACTER VARYING(N)

VARCHAR2

   

Y

   

CHARACTER VARYING

VARCHAR FOR BIT DATA

Y

       

BIT VARYING

VARGRAPHIC

Y

       

NCHAR VARYING

XMLTYPE

   

Y

   

XML


[1] Synonym for FLOAT

[2] Synonym for REAL

[3] Synonym for DOUBLE PRECISION

[4] Synonym for DECIMAL (9,2)

[5] Synonym for DECIMAL

[6] Oracle vastly prefers VARCHAR2

Each of the following sections lists platform-specific datatypes, the SQL2003 datatype category (if any), and pertinent details. Descriptions are provided for non-SQL2003 datatypes.

2.3.1 DB2 Datatypes

As shown below, DB2 supports a rich variety of datatypes, including most of the SQL2003 datatypes:


BIGINT (SQL2003 Datatype: BIGINT)

Stores signed or unsigned integers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. Uses 8 bytes of storage.


BLOB (SQL2003 Datatype: BLOB )

Holds variable-length binary data up to 2,147,483,647 bytes in length.


CHAR(n), CHARACTER(n) (SQL2003 Datatype: CHARACTER(n) )

Holds fixed-length character data up to 254 bytes in length. Uses ( n ) bytes of storage.


CHAR(n) FOR BIT DATA (SQL2003 Datatype: none )

Holds a fixed number of values. Uses ( n ) bytes of storage.


CLOB (SQL2003 Datatype: CLOB)

Holds variable-length character data up to 2,147,483,647 bytes in length.


DATALINK(n) (SQL2003 Datatype: DATALINK )

Stores a link to a file external to the database. Uses n +54 bytes of storage.


DATE (SQL2003 Datatype: DATE )

Holds a calendar date without time of day. Uses 4 bytes of storage.


DBCLOB(n) (SQL2003 Datatype: NCLOB )

Holds variable-length double-byte character data up to 107,3741,823 characters in length.


DEC(p,s), DECIMAL(p,s) (SQL2003 Datatype: DECIMAL, DECIMAL(p,s) )

May have a precision of 1 to 31 and a scale of 0 to 31. Uses the integral part of ( p /2)+1, where p is the precision bytes storage.


DOUBLE, DOUBLE PRECISION (SQL2003 Datatype: DOUBLE PRECISION )

Holds floating point numbers of -1.79769E+308 through 1.79769E+308. Uses 8 bytes of storage.


FLOAT (SQL2003 Datatype: FLOAT )

A synonym of DOUBLE PRECISION .


FLOAT (p) (SQL2003 Datatype: REAL, DOUBLE PRECISION )

Can range from 1 through 53. If p <= 24, then FLOAT (p) is a synonym for REAL . If 24 < p <= 53 then FLOAT (p) is a synonym for DOUBLE PRECISION .


GRAPHIC(n) (SQL2003 Datatype: NATIONAL CHARACTER )

Is not an image datatype, but rather holds fixed-length character strings (DBCS) up to 127 characters in length. Uses n *2 bytes of storage when using double-byte character sets or n bytes of storage when using single-byte character sets.


INT, INTEGER (SQL2003 Datatype: INTEGER )

Stores signed or unsigned integers between -2,147,483,648 and 2,147,483,647. Uses 4 bytes of storage.


LONG VARCHAR (SQL2003 Datatype: VARCHAR )

Holds variable-length character data up to 32,700 bytes in length. Uses 24 bytes of storage.


LONG VARCHAR FOR BIT DATA (SQL2003 Datatype: BIT VARYING )

Holds variable-length character data up to 32,700 bytes in length. Uses 24 bytes of storage.


LONG VARGRAPHIC (SQL2003 Datatype: none)

Holds variable-length double-byte character strings (DBCS) up to 16,350 characters in length. Uses 24 bytes of storage.


NUM(p,s), NUMERIC(p,s) (SQL2003 Datatype: NUMERIC, NUMERIC(p,s) )

Is a synonym for DECIMAL . Uses the integral part of ( p /2)+1, where p is the precision bytes storage.


REAL (SQL2003 Datatype: REAL )

Holds floating point numbers of -3.402E+38 through 3.402E+38. Uses 4 bytes of storage.


SMALLINT (SQL2003 Datatype: SMALLINT )

Stores signed or unsigned integers between -32,768 and 32,767. Uses 2 bytes of storage.


TIME (SQL2003 Datatype: TIME )

Holds the time of day. Uses 3 bytes of storage.


TIMESTAMP (SQL2003 Datatype: TIMESTAMP )

Stores the date and time. Uses 10 bytes of storage.


VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) (SQL2003 Datatype: CHARACTER VARYING, CHARACTER VARYING(n) )

Holds variable-length character data up to 32,672 bytes in length. Uses n +4 bytes of storage.


VARCHAR(n) FOR BIT DATA (SQL2003 Datatype: none )

Stores a variable number of values. Uses n +4 bytes of storage.


VARGRAPHIC(n) (SQL2003 Datatype: NCHAR VARYING )

Holds variable-length double-byte character data up to 16,336 characters in length. Uses ( n *2)+4 bytes of storage.

2.3.2 MySQL Datatypes

MySQL numeric datatypes support the following optional attributes:


UNSIGNED

The numeric value is assumed to be non-negative (positive or zero). For fixed-point datatypes like DECIMAL and NUMERIC , the space normally used to show a positive or negative condition of the numeric value can then be used as part of the value, providing a little extra numeric range in the column for DECIMAL and NUMERIC types. (There is no SIGNED optional attribute.)


ZEROFILL

Used for display formatting, this attribute tells MySQL that the numeric value is padded with zeros to its full size rather than spaces. ZEROFILL automatically forces the UNSIGNED attribute as well.

MySQL also enforces a maximum display size for columns of up to 255 characters. Columns longer than 255 characters are stored properly, but only 255 characters are displayed. Floating-point numeric datatypes may have a maximum of 30 digits after the decimal point.

The list below shows that MySQL supports most of the SQL2003 datatypes, plus several additional datatypes used to contain lists of values, as well as datatypes used for binary large objects ( BLOB s). Datatypes that extend the ANSI standard include TEXT , ENUM , SET , and MEDIUMINT . Special datatype attributes that go beyond the ANSI standard include AUTO_INCREMENT , BINARY , NULL , UNSIGNED , and ZEROFILL .


BIGINT[(n)] [UNSIGNED] [ZEROFILL] (SQL2003 Datatype: BIGINT)

Stores signed or unsigned integers. The signed range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. The unsigned range is 0 to 18,446,744,073,709,551,615. BIGINT can perform imprecise calculations due to rounding issues.


BIT, BOOL (SQL2003 Datatype: none)

Synonyms for TINYINT.


BLOB (SQL2003 Datatype: BLOB)

Stores up to 65,535 characters of data. Support for indexing BLOB columns is found only in MySQL Version 3.23.2 or greater (a feature not found in any other platform covered in this book). In MySQL, BLOB s are functionally equivalent to the MySQL datatype VARCHAR BINARY (discussed below) with the default upper limit. BLOB s always require case-sensitive comparisons. They differ from a MySQL VARCHAR BINARY column by not allowing DEFAULT values and not removing trailing spaces. Do not perform GROUP BY or ORDER BY on BLOB columns. They also are stored separately from their table, whereas all other datatypes in MySQL are stored in the table file structure itself.


CHAR(n)[BINARY], CHARACTER(n) [BINARY] (SQL2003 Datatype: CHARACTER(n))

Contains a fixed-length character string of 1 to 255 characters in length. CHAR pads with blank spaces when it stores values, but trims spaces upon retrieval as ANSI SQL2003 VARCHAR does. The BINARY option allows binary searches rather than dictionary-order, case-insensitive searches.


DATE ( SQL2003 Datatype: DATE )

Stores a date from 1000-01-01 to 9999-12-31 (delimited by quotes). MySQL displays these values by default as YYYY-MM-DD, though the user may specify some other display format.


DATETIME (SQL2003 Datatype: TIMESTAMP)

Stores date and time values within the range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59.


DECIMAL [(p[,s])] [ZEROFILL] (SQL2003 Datatype: DECIMAL(PRECISION, SCALE))

Stores exact numeric values as if they were strings, using a single character for each digit. Precision is 10 if omitted, and scale is 0 if omitted.


DOUBLE[(p,s)] [ZEROFILL], DOUBLE PRECISION[(p,s)] [ZEROFILL] (SQL2003 Datatype: DOUBLE PRECISION)

Holds double-precision numeric values and is otherwise identical to the double-precision FLOAT datatype, except that its allowable range is -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.


ENUM ("val1""val2," . . . n) (SQL2003 Datatype: none)

A datatype whose value must be one of those contained in the list of values ( expressed as strings but stored as integers), NULL, or an empty string( "" ) as an error value. Up to 65,535 distinct values are allowed.


FLOAT[(p)] [ZEROFILL], float(p,s) [ZEROFILL] (SQL2003 Datatype: FLOAT(P))

Stores floating-point numbers in the range -3.402823466E+38 to -1.175494351E-38 and 1.175494351E-38 to 3.402823466E+38. FLOAT without a precision, or with a precision of <= 24, is single precision. Otherwise, FLOAT is double precision. When specified alone, precision can range from 0 to 53. When you specify both precision and scale, precision may be as high as 255 and the scale may be as high as 253. All FLOAT calculations in MySQL are done with double precision and may, since FLOAT is an approximate datatype, encounter rounding errors.


I NT[EGER][(n)][UNSIGNED][ZEROFILL][AUTO_INCREMENT] (SQL2003 Datatype: INT, INTEGER)

Stores signed or unsigned integers within the range of -2,147,483,648 to 2,147,483,647 on ISAM tables. Unsigned, the range is 0 to 4,294,967,295 on ISAM tables. The range of values varies slightly on other types of tables. AUTO_INCREMENT is available to all of the INT variants. It creates a unique row identity for all new rows added to the table. (Refer to CREATE/ALTER TABLE Statement for more information on AUTO_INCREMENT .)


LONGBLOB (SQL2003 Datatype: BINARY LARGE OBJECT)

Stores BLOB data up to 4,294,967,295 characters in length. Note that this might be too much information for some client/server protocols to support.


LONGTEXT (SQL2003 Datatype: CLOB)

Stores TEXT data up to 4,294,967,295 characters in length. Note that this might be too much data for some client/server protocols to support.


MEDIUMBLOB (SQL2003 Datatype: none)

Stores BLOB data up to 16,777,215 characters in length.


MEDIUMTEXT (SQL2003 Datatype: none)

Stores TEXT data up to 16,777,215 characters in length.


MEDIUMINT[(n)] [UNSIGNED] [ZEROFILL] (SQL2003 Datatype: none)

Stores signed or unsigned integers within the range of 8,388,608 to -8,388,608. The unsigned range is 0 to 16,777,215.


NCHAR(n) [BINARY], [NATIONAL] CHAR(n) [BINARY] (SQL2003 Datatype: NCHAR(n))

Synonyms for CHAR . The NCHAR datatypes provide UNICODE support beginning in MySQL v4.1.


NUMERIC(p,s) (SQL2003 Datatype: DECIMAL(p,s))

A synonym of DECIMAL .


NVARCHAR(n) [BINARY], [national] VARCHAR(n) [BINARY], NATIONAL CHARACTER VARYING(n) [BINARY] (SQL2003 Datatype: NCHAR VARYING)

Synonyms for VARYING [BINARY] . Holds variable length character strings up to 255 characters in length. Values are stored and compared as case-insensitive unless the BINARY keyword is used.


REAL(p,s) (SQL2003 Datatype: REAL)

Is a synonym of DOUBLE PRECISION .


SET("val1," "val2," . . . n) (SQL2003 Datatype: none)

Is a CHAR datatype whose value must be equal to zero or more values specified in the list of values. Up to 64 items are allowed in the list of values.


SMALLINT[(n)] [UNSIGNED] [ZEROFILL] (SQL2003 Datatype: SMALLINT)

Stores signed or unsigned integers. The signed range is from -32768 to 32,767. The unsigned range is 0 to 65,535.


TEXT (SQL2003 Datatype: none)

Stores up to 65,535 characters of data. TEXT datatypes are stored separately from their tables, whereas all other datatypes are stored in their respective table file structures. TEXT is functionally equivalent to VARCHAR with no specific upper limit (besides the maximum size of the column), and requires case-insensitive comparisons. TEXT differs from a standard VARCHAR column by not allowing DEFAULT values, and by not removing trailing spaces. TEXT columns cannot be used in GROUP BY or ORDER BY clauses. In addition, support for indexing TEXT columns comes only in MySQL Version 3.23.2 or greater.

2.3.3 Oracle Datatypes

As shown below, Oracle supports a rich variety of datatypes, including most of the SQL2003 datatypes.


BFILE (SQL2003 Datatype: DATALINK )

Holds a pointer to a BLOB stored outside the database, but present on the local server, of up to 4 GB in size. The database streams input (but not output) access to the external BLOB . If you delete a row containing a BFILE value, only the pointer value is deleted. The actual file structure is not deleted.


BINARY_FLOAT (SQL2003 Datatype: FLOAT )

Holds a 32-bit floating point number.


BINARY_DOUBLE (SQL2003 Datatype: FLOAT )

Holds a 64-bit floating point number.


BLOB (SQL2003 Datatype: BLOB )

Holds a binary large object ( BLOB ) value of between 8 and 128 terabytes in size, depending on the database blocksize. In Oracle, large binary objects ( BLOB s, CLOB s, and NCLOB s) have the following restrictions:

  • They cannot be selected remotely.

  • They cannot be stored in clusters.

  • They cannot compose a varray.

  • They cannot be a component of an ORDER BY or GROUP BY clause in a query.

  • They cannot be used by an aggregate function in a query.

  • They cannot be referenced in queries using DISTINCT , UNIQUE , or joins.

  • They cannot be referenced in ANALYZE...COMPUTE or ANALYZE...ESTIMATE statements.

  • They cannot be part of a primary key or index key.

  • They cannot be used in the UPDATE OF clause in an UPDATE trigger.


CHAR(n)[BYTE CHAR], CHARACTER(n)[BYTE CHAR] (SQL2003 Datatype: CHARACTER(n) )

Holds fixed-length character data up to 2,000 bytes in length. BYTE tells Oracle to use bytes for the size measurement. CHAR tells Oracle to use characters for the size measurement.


CLOB (SQL2003 Datatype: CLOB )

Stores a character large object ( CLOB ) value of between 8 and 128 terabytes in size, depending on the database blocksize. See BLOB for a list of restrictions on the use of the CLOB type.


DATE (SQL2003 Datatype: DATE )

Stores a valid date and time within the range of 4712BC-01-01 00:00:00 to 9999AD-12-31 23:59:59.


DECIMAL(p,s) (SQL2003 Datatype: DECIMAL(p,s) )

Is a synonym of NUMBER that accepts precision and scale arguments.


DOUBLE PRECISION (SQL2003 Datatype: DOUBLE PRECISION )

Stores floating point values with double precision, the same as FLOAT(126).


FLOAT(n) (SQL2003 Datatype: FLOAT(n) )

Stores floating-point numeric values with a binary precision of up to 126.


INTEGER(n) (SQL2003 Datatype: INTEGER )

Stores signed and unsigned integer values with a precision of up to 38. INTEGER is treated as a synonym for NUMBER .


INTERVAL DAY (n) TO SECOND (x) (SQL2003 Datatype: INTERVAL )

Stores a time span in days, hours, minutes, and seconds where n is the number of digits in the day field (0 to 9 acceptable, 2 is the default) and x is the number of digits used for fractional seconds in the seconds field (0 to 9 acceptable, 6 is the default).


INTERVAL YEAR (n) TO MONTH (SQL2003 Datatype: INTERVAL )

Stores a time span in years and months where n is the number of digits in the year field. The value n can be 0 to 9, with a default of 2.


LONG (SQL2003 Datatype: none)

Stores variable-length character data up to 2 gigabytes in size. Please note, however, that LONG is not scheduled for long- term support by Oracle. Use another datatype, such as CLOB , instead of LONG , whenever possible.


LONG RAW (SQL2003 Datatype: none)

Stores raw variable-length binary data up to 2 gigabytes in size. LONG RAW and RAW are typically used to store graphics, sounds, documents, and other large data structures. BLOB is preferred over LONGRAW in Oracle because BLOB has fewer restrictions on its use. LONGRAW is also deprecated.


NATIONAL CHARACTER VARYING (n), NATIONAL CHAR VARYING (n),

 


NCHAR VARYING (n) (SQL2003 Datatype: NCHAR VARYING (n) )

The same as NVARCHAR2 .


NCHAR(n), NATIONAL CHARACTER(n),NATIONAL CHAR(n) (SQL2003 Datatype: NATIONAL CHARACTER )

Holds UNICODE character data of 1 to 2,000 bytes in length. Default size is 1 byte.


NCLOB (SQL2003 Datatype: NCLOB )

Represents a CLOB that supports multibyte and UNICODE values of between 8 and 128 terabytes in size, depending on the database blocksize. See BLOB for a list of restrictions on the use of the CLOB type.


NUMBER (p,s), NUMERIC (p,s) (SQL2003 Datatype: NUMERIC (p,s) )

Stores a number with a precision of 1 to 38 and a scale of -84 to 127.


NVARCHAR2(n) (SQL2003 Datatype: none)

Represents Oracle's preferred UNICODE variable-length character datatype. It can hold 1 to 4,000 bytes.


RAW(n) (SQL2003 Datatype: none)

Stores raw, variable-length binary data up to 2,000 bytes in size. The value n is the specified size of the datatype. RAW is also deprecated in Oracle 10 g . See LONG RAW .


REAL (SQL2003 Datatype: REAL )

Stores floating-point values as single-precision and is the same as FLOAT (63).


ROWID (SQL2003 Datatype: none)

Represents a unique, base-64 identifier for each row in a table, often used in conjunction with the ROWID pseudocolumn.


SMALLINT (SQL2003 Datatype: SMALLINT )

The same as INT EGER .


TIMESTAMP(n){[WITH TIME ZONE] [WITH LOCAL TIME ZONE]} (SQL2003 Datatype: TIMESTAMP[WITH TIME ZONE] )

A full date and time value where n is the number of digits (0 to 9 are acceptable, 6 is the default) in the fractional part of the seconds field. WITH TIME ZONE stores whatever time zone you pass to it (the default is your session time zone) and returns a time value in that same time zone. WITH LOCAL TIME ZONE stores data in the time zone of the current session and returns data in the time zone of the user's session.


UROWID [(n)] (SQL2003 Datatype: none)

Stores a base-64 value showing the logical address of the row in its table. It defaults to 4,000 bytes in size, but you may optionally specify its size up to 4,000 bytes.


VARCHAR(n), CHARACTER VARYING(n), CHAR VARYING(n) (SQL2003 Datatype: CHARACTER VARYING(n) )

Holds variable-length character data of 1 to 4,000 bytes in size.

Oracle does not recommend using VARCHAR . Oracle has encouraged the use of VARCHAR2 instead for many years.



VARCHAR2(n [ BYTE CHAR ] ) (SQL2003 Datatype: CHARACTER VARYING(n) )

Holds variable-length character data up to 4,000 bytes in length as defined by n . BYTE tells Oracle to use bytes for the size measurement. CHAR tells Oracle to use characters for the size measurement. If you use CHAR , Oracle internally must still transform that into some number of bytes, which is then subject to the 4,000-byte upper limit.


XMLTYPE (SQL2003 Datatype: XML)

Stores XML data within the Oracle database. The XML data is accessed using XPath expressions as well as a number of built-in XPath functions, SQL functions, and PL/SQL packages. The XMLTYPE datatype is a system-defined type, so it is usable as an argument in functions, or as a datatype of column in a table or view. When used in a table, the data can be stored in a CLOB column or object-relationally.

2.3.4 PostgreSQL Datatypes

PostgreSQL database supports most SQL2003 datatypes, plus an extremely rich set of datatypes that store spatial and geometric data. PostgreSQL sports a rich set of operators and functions especially for the geometric datatypes, including capabilities such as rotation, finding intersections, and scaling. PostgreSQL also supports additional versions of existing datatypes that are smaller and take up less disk space than their corresponding primary datatypes. For example, PostgreSQL offers several variations on INTEGER to accommodate small or large numbers and thereby consume proportionally less or more space.


BIGSERIAL

See SERIALS .


BIT (SQL2003 Datatype: BIT )

A fixed-length bit string.


BIT VARYING(n), varbit(n) (SQL2003 Datatype: BIT VARYING )

Denotes a variable-length bit string n bits in length.


BOOL, BOOLEAN (SQL2003 Datatype: BOOLEAN )

Stores a logical Boolean (true/false/unknown) value. The keywords TRUE and FALSE are preferred, but PostgreSQL supports the following valid literal values for the "true" state: TRUE , t, true, y, yes, and 1. Valid "false" values are: FALSE , f, false, n, no, and 0.


BOX ( (x1, y1), (x2, y2) ) (SQL2003 Datatype: none)

Stores the values of a rectangular box in a 2D plane. Values are stored in 32 bytes and are represented as ((x1,y1),(x2,y2)) , signifying the opposite corners of the box (upper-right and lower-left corners, respectively). The outer parentheses are optional.


BYTEA (SQL2003 Datatype: BINARY LARGE OBJECT )

Raw, binary data such as that used to store graphics, sound, or documents. For storage, this datatype requires 4 bytes plus the actual size of the binary string.


CHAR(n), CHARACTER(n) (SQL2003 Datatype: CHARACTER(n) )

Contains a fixed-length character string padded with spaces up to a length of n . Attempting to insert a value longer than n results in an error (unless the extra length is composed of spaces, which are then truncated such that the result fits in n characters).


CIDR (x.x.x.x/y) (SQL2003 Datatype: none)

Describes an IP-Version 4 network or host address in a 12-byte storage space. The range is any valid IPv4 network address. Data in CIDR datatypes is represented as x.x.x.x/y where the x s are the IP address and y is the number of bits in the netmask . CIDR does not accept nonzero bits to the right of a zero bit in the netmask.


CIRCLE x, y, r (SQL2003 Datatype: none)

Describes a circle in a 2D plane. Values are stored in 24 bytes of storage space and are represented as: x , y , r . The x , y value represents the coordinates of the center of the circle, while r represents the length of the radius. Parentheses or arrow brackets may optionally delimit the values for x, y , and r .


DATE (SQL2003 Datatype: DATE )

Holds a calendar date (year, day, and month) without the time of day in a 4-byte storage space. Dates must be between 4713 BC and 32767 AD. DATE 's lowest resolution, naturally, is to the day.


DATETIME (SQL2003 Datatype: TIMESTAMP )

Holds a calendar date with a specific time of day.


DECIMAL [(p,s)], NUMERIC[(p,s)] (SQL2003 Datatype: DECIMAL (PRECISION, SCALE), NUMERIC (p,s) )

Stores exact numeric values with a precision ( p ) as high as 9 and a scale ( s ) of 0, with no upper limit.


FLOAT4, REAL (SQL2003 Datatype: FLOAT(p) )

Stores floating-point numbers with a precision of 8 or less and 6 decimal places.


FLOAT8, DOUBLE PRECISION (SQL2003 Datatype: FLOAT(p), 7 <= p < 16 )

Stores floating-point numbers with a precision of 16 or less, and 15 decimal places.


INET (x.x.x.x/y)

Stores an IP-Version 4 network or host address in a 12-byte storage space. The range is any valid IPv4 network address. The x 's represent the IP address, and y is the number of bits in the netmask. The netmask defaults to 32. Unlike CIDR , INET accepts nonzero bits to the right of the netmask.


SMALLINT (SQL2003 Datatype: SMALLINT )

Stores signed or unsigned 2-byte integers within a range of -32,768 to 32,767. INT2 is a synonym.


INTEGER (SQL2003 Datatype: INTEGER )

Stores signed or unsigned 4-byte integers within a range of -2,147,483,648 to 2,147,483,647. INT4 is a synonym.


INT8 (SQL2003 Datatype: none)

Stores signed or unsigned 8-byte integers with a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.


INTERVAL(p) (SQL2003 Datatype: none)

Holds general-use time-span values within the range of -178,000,000 to 178,000,000 years in a 12-byte storage space. INTERVAL' s lowest resolution is to the microsecond. This is a different datatype than the ANSI standard, which requires an interval qualifier such as INTERVAL YEAR TO MONTH .


LINE ( ( x1, y1) , (x2, y2) ) (SQL2003 Datatype: none)

Holds line data, without endpoints, in 2D plane values. Values are stored in 32 bytes and are represented as ((x1,y1), (x2,y2)) , indicating the start and end points of a line. Parentheses are optional for line syntax.


LSEG ( ( x1, y1) , (x2, y2) ) (SQL2003 Datatype: none)

Holds line segment ( LSEG ) data, with endpoints, in a 2D plane. Values are stored in 32 bytes and are represented as ((x1,y1), (x2,y2)). Parentheses are optional for LSEG syntax. To those who are interested, the "line segment" is what most people traditionally think of as a line. For example, the lines on a playing field are actually line segments.

In true geometric nomenclature , a line stretches to infinity, having no terminus at either end, while a line segment has end points. PostgreSQL allows datatypes for both, but they are functionally equivalent.



MACADDR (SQL2003 Datatype: none)

Holds a value for the MAC address of a computer's network interface card in a 6-byte storage space. MACADDR accepts a number of industry standard representations, such as:

08002B:010203
08002B-010203
0800.2B01.0203
08-00-2B-01-02-03
08:00:2B:01:02:03

MONEY, DECIMAL(9,2) (SQL2003 Datatype: none)

Stores U.S.-style currency values in the range of (-21,474,836.48 to 21,474,836.47).


NUMERIC [ (p, s) ], DECIMAL [ (p, s) ] (SQL2003 Datatype: none)

Stores exact numeric values with a precision ( p ) and scale ( s ).


OID (SQL2003 Datatype: none)

Stores unique object identifiers.


PATH ( (x1, y1), ...n), Path [ (x1, y1), ...n] (SQL2003 Datatype: none)

Describes an open and closed geometric path in a 2D plane. Values are represented as [ (x1, y1),...] and consume 4 + 32 n bytes of storage space. Each (x,y) value represents a point on the path. Paths are either open, where the first and last points do not intersect, or closed, where the first and last points do intersect. Parentheses are used to encapsulate closed paths, while brackets encapsulate open paths.


POINT (x, y) (SQL2003 Datatype: none)

Stores values for a geometric point in a 2D plane in a 16-byte storage space. Values are represented as ( x,y ). The point is the basis for all other two-dimensional spatial datatypes supported in PostgreSQL. Parentheses are optional for point syntax.


POLYGON ( (x1, y1), ...n) (SQL2003 Datatype: none)

Stores values for a closed geometric path in a 2D plane using 4 + 32 n bytes of storage. Values are represented as [(x1,y1),...]. POLYGON is essentially a closed path datatype.


SERIAL, SERIAL4 (SQL2003 Datatype: none)

Stores an autoincrementing, unique, integer ID for indexing and cross-referencing. These types store up to 4 bytes of data (a range of numbers from 1 to 2,147,483,647). Tables defined with this datatype cannot be directly dropped. You must first issue the DROP SEQUENCE command, then follow up with the DROP TABLE command.


SERIAL8, BIGSERIAL (SQL2003 Datatype: none)

Stores an autoincrementing, unique integer ID for indexing and cross-referencing. It stores up to 8 bytes of data (a range of numbers from 1 to 9,223,372,036,854,775,807). Tables defined with this datatype cannot be directly dropped. You must first issue the DROP SEQUENCE command, and then follow up with the DROP TABLE command.


TEXT (SQL2003 Datatype: CLOB )

Stores large, variable-length, character-string data up to 1 gigabyte. PostgreSQL automatically compresses TEXT strings, so the disk size may be less than the string size.


TIME [ (p) ] [WITHOUT TIME ZONE WITH TIME ZONE] (SQL2003 Datatype: TIME )

Holds the time of day and stores either no time zone (using 8 bytes of storage space) or the time zone of the database server (using 12 bytes of storage space). The allowable range is from 00:00:00.00 to 23:59:59.99. The lowest granularity is 1 microsecond. Note that time zone information on most Unix systems is available only for the years 1902 through 2038.


TIMESPAN (SQL2003 Datatype: none)

Holds a value that represents a specific span of time. The ANSI datatype most like PostgreSQL's TIMESPAN is INTERVAL .


TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE WITH TIME ZONE ] (SQL2003 Datatype: TIMESTAMP [WITH TIME ZONE WITHOUT TIME ZONE] )

Stores the date and time and stores either no time zone or the time zone of the database server. The range of values is from 4713 BC to 1465001 AD. TIMESTAMP uses 8 bytes of storage space per value. The lowest granularity is 1 microsecond. Note that time zone information on most Unix systems is available only for the years 1902 through 2038.


TIMETZ (SQL2003 Datatype: TIME WITH TIME ZONE )

Holds the time of day, including the time zone.


VARCHAR(n), CHARACTER VARYING(n) (SQL2003 Datatype: CHARACTER VARYING(n) )

Stores variable-length character strings up to a length of n . Trailing spaces are not stored.

2.3.5 SQL Server Datatypes

The list below shows that Microsoft SQL Server supports most SQL2003 datatypes. SQL Server supports additional datatypes used to uniquely identify rows of data within a table and across multiple servers, such as UNIQUEIDENTIFIER , in support of Microsoft's hardware philosophy of "scale-out" (that is, deploying on many Intel-based servers), rather than " scale-up " (deploying on a single, huge, high-end Unix server or a Windows Data Center Server).

An interesting side note about SQL Server dates: SQL Server supports dates starting at the year 1753. You can't store dates prior to that year using any of SQL Server's date datatypes. Why not? The rationale is that the English-speaking world started using the Gregorian calendar in 1753 (the Julian calendar was used prior to September, 1753) and converting dates prior to Julian to the Gregorian calendar can be quite challenging.



BIGINT (SQL2003 Datatype: BIGINT)

Stores signed and unsigned integers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 using 8 bytes of storage space. See INT for IDENTITY property rules that also apply to BIGINT .


BINARY[(n)] (SQL2003 Datatype: BLOB )

Stores a fixed-length binary value of 1 to 8,000 bytes in size. BINARY datatypes consume n + 4 bytes of storage space.


BIT (SQL2003 Datatype: BOOLEAN )

Stores 1, 0, or NULL to indicate "unknown." Up to 8 BIT columns on a single table will be stored in a single byte. An additional 8 BIT columns consume one more byte of storage space. BIT columns cannot be indexed.


CHAR[(n)], CHARACTER[(n)] (SQL2003 Datatype: CHARACTER(n) )

Holds fixed-length character data of 1 to 8,000 characters in length. Any unused space is, by default, padded with spaces. (You can disable the automatic padding of spaces.) Storage size is n bytes.


CURSOR (SQL2003 Datatype: none)

A special datatype used to describe a cursor as a variable or stored procedure OUTPUT parameter. It cannot be used in a CREATE TABLE statement. The CURSOR datatype is always nullable.


DATETIME (SQL2003 Datatype: TIMESTAMP )

Holds date and time data within the range of 1753-01-01 00:00:00 through 9999-12-31 23:59:59. Values are stored in an 8-byte storage space.


DECIMAL (p,s), DEC (p,s), NUMERIC (p,s) (SQL2003 Datatype: DECIMAL (p,s), NUMERIC (p,s) )

Stores decimal values up to 38 digits long. The values p and s define precision and scale, respectively. The default value for scale is 0. The precision of the datatype determines how much storage space it will consume:

Precision 1-9 uses 5 bytes
Precision 10-19 uses 9 bytes
Precision 20-28 uses 13 bytes
Precision 29-39 uses 17 bytes

See INT for IDENTITY property rules that also apply to DECIMAL .


DOUBLE PRECISION (SQL2003 Datatype: none)

A synonym for FLOAT (53).


FLOAT [ (n) ] (SQL2003 Datatype: FLOAT, FLOAT (n) )

Holds floating-point numbers in the range -1.79E+308 through 1.79E+308. Precision, represented by n , may be 1 to 53. Storage size is 4 bytes for 7 digits, where n is 1 to 24. Anything larger requires 8 bytes of storage.


IMAGE (SQL2003 Datatype: BLOB )

Stores a variable-length binary value up to 2,147,483,647 bytes in length. This datatype is commonly used to store graphics, sounds, and files like MS-Word documents and MS-Excel spreadsheets. IMAGE cannot be freely manipulated. IMAGE and TEXT columns have a lot of constraints on how they can be used. See TEXT for a list of the commands and functions that work on an IMAGE datatype.


INT [IDENTITY [ (seed, increment) ] (SQL2003 Datatype: INTEGER )

Stores signed or unsigned integers between -2,147,483,648 and 2,147,483,647 in 4 bytes of storage space. All integer datatypes, as well as the decimal type, support the IDENTITY property. An identity is an automatically incrementing row identifier. Refer to the CREATE/ALTER TABLE Statement .


MONEY (SQL2003 Datatype: none)

Stores monetary values in a range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Values are stored in an 8-byte storage space.


NCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER(n) (SQL2003 Datatype: NATIONAL CHARACTER (n) )

Holds fixed-length UNICODE data up to 4,000 characters in length. NCHAR consumes twice n in storage space.


NTEXT, NATIONAL TEXT (SQL2003 Datatype: NCLOB)

Holds UNICODE text passages up to 1,073,741,823 characters in length. See TEXT for rules about the commands and functions available for NTEXT .


NUMERIC(p,s) (SQL2003 Datatype: DECIMAL(p,s) )

A synonym for DECIMAL . See INT for rules about the IDENTITY property.


NVARCHAR(n), NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n) (SQL2003 Datatype: NATIONAL CHARACTER VARYING(n) )

Holds variable-length UNICODE data up to 4,000 characters in length. Storage space consumed is double the character length (characters * 2) inserted into the field. The system setting SET ANSI_PADDING is always enabled (ON) for NCHAR and NVARCHAR fields in SQL Server.


REAL, FLOAT(24) (SQL2003 Datatype: REAL )

Holds floating point numbers of -3.40E + 38 through 3.40 E+38 in a 4-byte storage space. REAL is functionally equivalent to FLOAT(24).


ROWVERSION (SQL2003 Datatype: none)

A unique number within a database that is updated whenever a row is updated. Called TIMESTAMP in earlier versions.


SMALLDATETIME (SQL2003 Datatype: none)

Holds date and time data within the range of `1900-01-01 00:00' through `2079-06-06 23:59' down to the nearest minute. (Minutes are rounded down when seconds are 29.998 or less. Otherwise, they are rounded up.) Values are stored in a 4-byte storage space.


SMALLINT (SQL2003 Datatype: SMALLINT )

Stores signed or unsigned integers between -32,768 and 32,767 in 2 bytes of storage space. See INT for rules about the IDENTITY property, which applies to this type.


SMALLMONEY (SQL2003 Datatype: none)

Stores monetary values within the range of 214,748.3648 and -214,748.3647. Values are stored in a 4-byte storage space.


SQL_VARIANT (SQL2003 Datatype: none)

Stores values of other SQL Server-supported datatypes, except TEXT , NTEXT , ROWVERSION , and other SQL_VARIANT s. It can store up to 8,016 bytes of data and supports NULL and DEFAULT values. SQL_VARIANT is used in columns, parameters, variables , and return values of functions and stored procedures.


TABLE (SQL2003 Datatype: none)

A special datatype that stores a result set for a later process. It is used solely in procedural processing and cannot be used in a CREATE TABLE statement. This datatype alleviates the need for temporary tables in many applications. It can reduce the need for procedure recompiles, thus speeding execution of stored procedures and user-defined functions.


TEXT (SQL2003 Datatype: CLOB )

Stores very large passages of text up to 2,147,483,647 characters in length. TEXT and IMAGE values are often more difficult to manipulate than, say, VARCHAR values. For example, you cannot place an index on a TEXT or IMAGE column. TEXT can be manipulated using the functions DATALENGTH , PATINDEX , SUBSTRING , TEXTPTR , and TEXTVALID as well as the commands READTEXT , SET TEXTSIZE , UPDATETEXT , and WRITETEXT .


TIMESTAMP (SQL2003 Datatype: TIMESTAMP )

Stores an automatically generated binary number that guarantees uniqueness in the current database and is therefore different from the ANSI TIMESTAMP datatype. TIMESTAMP s consume 8 bytes of storage space. ROWVERSION is now preferred over TIMESTAMP to uniquely track each row.


TINYINT (SQL2003 Datatype: none)

Stores unsigned integers between 0 and 255 in 1 byte of storage space. See the entry for INT to see rules about the IDENTITY property, which applies to this type.


UNIQUEIDENTIFIER (SQL2003 Datatype: none)

Represents a value that is globally unique across all databases and all servers. Values are represented as xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx , in which each x is a hexadecimal digit in the range 0-9 or a-f. The only operations allowed against UNIQUEIDENTIFIERs are comparisons and NULL checks. Column constraints and properties are allowed on UNIQUEIDENTIFIER columns, with the exception of the IDENTITY property.


VARBINARY[(n)] (SQL2003 Datatype: BLOB )

Describes a variable-length binary value up to 8,000 bytes in size. Storage space consumed is equivalent to the size of the data inserted, plus 4 bytes.


VARCHAR[(n)], CHAR VARYING[(n)], CHARACTER VARYING [(n)] (SQL2003 Datatype: CHARACTER VARYING(n) )

Holds fixed-length character data of 1 to 8,000 characters in length. Storage space is the actual size of the value entered in bytes, not the value of n .



SQL in a Nutshell
SQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596518846
EAN: 2147483647
Year: 2003
Pages: 78

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net