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.

Tip 

MS SQL Server BIT data type is yet another way to simulate SQL99 BOOLEAN data type. 1 corresponds to TRUE and 0 denotes FALSE.

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).

Tip 

Another related concept in MS SQL Server (and DB2 UDB) is an identity column. It is not a data type, but rather a special numeric column property that requires the column to contain system-generated sequential values that uniquely identify each row within table. More about identity columns is in Chapter 4.