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
|
Y
|
FLOAT
|
ENUM
| |
Y
| | | |
none
|
FLOAT
|
Y
|
Y
|
Y
| |
Y
|
DOUBLE PRECISION
|
FLOAT4
| | | |
Y
| |
FLOAT(P)
|
FLOAT8
| | | |
Y
| |
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
|
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
|
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
|
Y
|
Y
|
CHARACTER VARYING(N)
|
VARCHAR2
| | |
Y
| | |
CHARACTER VARYING
|
VARCHAR FOR BIT DATA
|
Y
| | | | |
BIT VARYING
|
VARGRAPHIC
|
Y
| | | | |
NCHAR VARYING
|
XMLTYPE
| | |
Y
| | |
XML
|
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
(BLOBs). 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, BLOBs are functionally
equivalent to the MySQL datatype VARCHAR BINARY
(discussed below) with the default upper limit.
BLOBs 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.
- INT[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
(BLOBs, CLOBs, and
NCLOBs) 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
10g. 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 INTEGER.
- 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 xs 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 +
32n 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 +
32n 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_VARIANTs. 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.
TIMESTAMPs 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.
|