O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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)

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required