SQL99 and Vendor-Specific Datatypes

The previous section mentioned that a table could contain one or many columns, each with a single defining datatype. In real world applications, datatypes provide some control and efficiency as to how tables are defined. Using specific datatypes enables better, more understandable queries and controls the integrity of data.

The tricky thing about SQL99 datatypes is that they do not map directly to an identical implementation in a given vendor’s product. Although the vendors provide “datatypes” that correspond to the SQL99 datatypes, these vendor-specific datatypes are not true SQL99 datatypes. Nonetheless, each vendor’s datatypes are close enough to the standard to be both easily understandable and job-ready.

The official SQL99 datatypes (as opposed to vendor-specific) fall into the general categories described in Table 2.1.

Table 2-1. SQL99 Datatypes

Category

Example Datatypes and Abbreviations

Description

binary

binary large object (BLOB)

This datatype stores binary string values in hexadecimal format.

bit string

bit

bit varying

These datatypes store either binary or hexadecimal data. BIT has a fixed length, while BIT VARYING has a variable length.

boolean

boolean

This datatype stores truth values — either TRUE, FALSE, or UNKNOWN.

character

char

character varying (VARCHAR)

national character (NCHAR)

national character varying (NVARCHAR)

character large object (CLOB)

national character large object (NCLOB)

Get SQL in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.