SQL-99 datatypes

BIT. Bit strings are just sequences of bits. A one-bit sequence (0 or 1) is usually for "logical" information. Example of definition of a table with an 8-bit column:

CREATE TABLE TABLE_1 (COLUMN_1 BIT(8));

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (B'01000100');

BLOB. Binary strings are very large sequences of bits, generally images. Example of definition of a table with a 10,000 octet binary column:

CREATE TABLE TABLE_1 (COLUMN_1 BLOB(10000));

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (X'49FE');

CHAR. Use this for text of any description. There are several options, including the specification that storage should be variable size and the specification that storage should be in a certain character set Click here for description of character sets and collations . Example of definition of a table with a 2-character column:

CREATE TABLE TABLE_1 (COLUMN_1 CHAR(2));

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES ('XX');

CLOB. Large character strings are for text that includes control characters; there is no collation attached to the data. Example of definition of a table with a 10,000 octet character column:

CREATE TABLE TABLE_1 (COLUMN_1 CLOB(10000));

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES ('This is a large string.');

DECIMAL and NUMERIC. Use these for numbers with decimal precision, possibly with decimal points. Example of definition of a table with a 3-decimal-digit column:

CREATE TABLE TABLE_1 (COLUMN_1 DECIMAL(3));

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (300);

INTEGER and SMALLINT. Use these for numbers with binary precision, without decimal points. A number which has a binary precision can contain a certain number of bits, as opposed to a certain number of decimal digits (which is what DECIMAL and NUMERIC are for). SQL-92 allows variation, but THE OCELOT SQL DBMS follows the common convention that SMALLINTs have 16 bits and INTEGERs have 32 bits. Example of definition of a table with a 16-bit column:

CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT);

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (32767);

FLOAT and REAL and DOUBLE. These are sometimes known as the "approximate numeric data types", or more precisely as the "approximate numeric data type" because there's really only one -- FLOAT. REAL and DOUBLE PRECISION are just shorthands for shortish and longish floats. SQL-92 allows variation; THE OCELOT SQL DBMS treats REAL as a shorthand for FLOAT(24) and DOUBLE PRECISION as a shorthand for FLOAT(53). The numbers in parentheses here are the size of the mantissa in bits, and the sizes are IEEE norms. Examples of equivalent definitions of a table with a short approximate-numeric column:

CREATE TABLE TABLE_1 (COLUMN_1 FLOAT(24));

CREATE TABLE TABLE_1 (COLUMN_1 REAL);

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (-1E+03);

DATE. The mythical "year 2000 crisis" didn't affect SQL-92 dates -- they have four-digit year fields, and the rules say that compliant DBMSs must calculate leap years correctly. Example of a definition of a table with a date column:

CREATE TABLE TABLE_1 (COLUMN_1 DATE);

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (DATE '1993-01-02');

TIME. Times always have "seconds" fields (they may also have "parts-of-seconds" fields and "time zones" but we aren't going into details here). Example of a definition of a table with a time column:

CREATE TABLE TABLE_1 (COLUMN_1 TIME);

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (TIME '13:14:15');

TIMESTAMP. Timestamps are a combination of date plus time (they can have post-decimal digits too, again we omit the details). Example of a definition of a table with a timestamp column:

CREATE TABLE TABLE_1 (COLUMN_1 TIMESTAMP);

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (TIMESTAMP '1993-01-02 13:14:15.000001');

INTERVAL. Put simply, intervals are differences between two dates, two times, or two timestamps -- but it's wrong to put it simply, because there are baroque rules and caveats in the Standard. Example of a definition of a table with an INTERVAL column:

CREATE TABLE TABLE_1 (COLUMN_1 INTERVAL YEAR TO MONTH);

Example of an assignment of a literal value to the column:

INSERT INTO TABLE_1 VALUES (INTERVAL '01-01' YEAR TO MONTH);

Copyright (c) 2000-2002 by Ocelot Computer Services Inc. All rights reserved.

Return to Ocelot home page