SQL Bible [Electronic resources] نسخه متنی

Other Data Types

We covered all the major SQL data types in
detail. Now let's briefly describe a couple more that either are not
implemented by any of the major vendors or are implemented in such a way that
the data type name would not match SQL99 standards.

BOOLEAN

SQL99 has a special
BOOLEAN data type with a range that
includes only two values:
TRUE and
FALSE. Oracle, DB2, and Microsoft SQL
Server don't have a
BOOLEAN data type. (Or, to be more
precise, DB2 has it, but for internal use only, i.e., you cannot declare a
column of type
BOOLEAN.) But the
BOOLEAN data type can be easily
simulated, for example by using a user-defined data type of type
VARCHAR that only allows
FALSE and
TRUE for its values.

This example illustrates how to do it in
MS SQL Server:

CREATE RULE bool_rule AS @list
in ('TRUE', 'FALSE') sp_addtype BOOLEAN, 'VARCHAR(5)', 'NULL' sp_bindrule
'bool_rule', 'BOOLEAN'

Now you can use it just as another data
type in your instance of MS SQL Server.

ROWID

ROWID is a
special Oracle data type to store unique addresses for each row in the
database. Tables can be created with
ROWID columns, but that's not
recommended.

UROWID

UROWID is
similar to
ROWID but used for index-organized
tables.

BFILE

BFILE
Oracle data type enables read-only access to binary files stored outside the
Oracle database.

DATALINK

DATALINK is
an DB2 data type to manage large objects in the form of external files. The
files can reside in a file system on the same server or on a remote server.
Internal database functions are used to manipulate
DATALINK columns.

BIT

BIT data
type in MS SQL Server stores a bit of data (0 or 1) and does not correspond to
previously described SQL99
BIT. The literal value for bit is a
single character from its range optionally enclosed into single quotes.

TIMESTAMP

TIMESTAMP
data type in MS SQL Server is not the same as
TIMESTAMP SQL99 data type. You can only
have one column per table of type
TIMESTAMP. It exposes automatically
generated binary numbers (unique within a database) and is basically used to
uniquely identify a database row in a manner similar to (but not identical to)
Oracle's
ROWID and primarily serves version
control purposes. The main difference between
TIMESTAMP and
ROWID is that the value of a
TIMESTAMP column gets updated every time
the row is changed, whereas Oracle's
ROWID is assigned to a row for as long as
the row exists.

Microsoft is planning to replace this
data type with a
ROWVERSION data type in future releases
for SQL99 compliance. Currently
ROWVERSION is a synonym to
TIMESTAMP.

The storage size of
TIMESTAMP data type is 8 bytes; it is
semantically identical to binary(8).