SQL Data Types Overview

In SQL, as in many other programming languages, every piece of data has a particular type. For example, 11 is an integer, and “Wall Street” is a text string. The SQL standard defines many data types to accommodate almost any type of data.

Ads

Some cases arise where the SQL standard does not adequately support certain types of data, however, and in these cases, the individual implementations of SQL extend data type definitions to include certain custom data types.

In this article, the standard SQL data types and NULL values will be explained, and some of the more popular custom data types will also be covered. In addition, type casting and coercion with the CAST function will be discussed.

Standard Data Types

The standard SQL data types can be broken up into roughly four categories: Boolean and binary, character, numeric, and date and time.

Boolean & Binary

    boolean

Also known as bool, this type allows a value of either true or false. SQL implementations tend to allow different sets of values for the data, including true and false, t and f, yes and no, and 1 and 0, respectively, depending on the database system.

    bit(n)

This represents a bit string, or a string of zeros and ones, which could look something like 01101001. The maximum number of bits in the string is specified by n. Many implementations of this data type will pad a bit string with zeros on the right-hand side if a bit string has fewer than n bits. This may not be desirable behavior, depending on the application using the bit string, so it is prudent for developers to remember this. An example of this behavior is shown in Figure 1.

In Figure 1, we see the decimal number 3850, represented by binary 111100001010, cast to the decimal number 61600, represented in 16 bits by binary 1111000010100000. For purposes of left-hand bit shifting, the padding presents no problem, but it will cause errors if used for numeric computation or right-hand bit shifting.

    bit varying(n)

Ads

Also called varbit(n), this data type allows a variable-length bit string, with a length of up to n bits. In some database systems, varbit can be specified without a maximum length, and that field will accept bit strings of (ideally) unlimited length. These bit strings are not padded.

Next Page: SQL Character and Numeric Data Type