Integers are the whole numbers that we have been accustomed to from the earliest days of our childhood: 1, 2, 3, and so on. In standard SQL, there are three integer data types. INTEGER and SMALLINT have been standard all along, and BIGINT appears to have been added in either the SQL-1999 or SQL-2003 standard.^[9]

Decimal numbers have two parts: the total number of digits, and the number of digits to the right of the decimal point; the decimal point isn’t actually stored. For example, the number 9.37 has three total digits, of which two are to the right of the decimal point.

There are two, almost identical, kinds of decimal data type: NUMERIC and DECIMAL. Both data types have the same format:

NUMERIC(p[,s])
DECIMAL(p[,s])

The mandatory parameter p above, represents the precision: the total number of digits allowed. The optional parameters (which defaults to 0 if omitted) represents the scale: the total number of digits to the right of the decimal point.

Standard SQL says that the difference between NUMERIC and DECIMAL is implementation dependent. NUMERIC columns must have the exact precision specified, but DECIMAL columns might have a larger precision than specified if this is more efficient or convenient for the database system. In practice, they behave identically. My personal preference is DECIMAL.

NUMERIC and DECIMAL data types each allow both positive and negative values, and have the same range of possible values. However, the size of this range varies from one database system to another. PostgreSQL, for example, allows a precision of 1,000 digits. In practice, you’ll rarely approach the limits of the range, whatever they are.

Let’s look at a few quick examples of DECIMAL data types.

To define a column which will hold a value such as 9.37, we could employ DECIMAL(3,2) as the data type. The precision and scale of 3 and 2 mean that:

Note that DECIMAL(3,2) is inadequate for holding a value such as 12.34, because 12.34 has two digits to the left of the decimal point, and we allowed for only one. Attempting to insert this value usually results in an error message about “arithmetic overflow.”

Nor can DECIMAL(3,2) properly hold a value such as 0.567, because even though there are only three significant digits in total, the column can hold only two positions to the right of the decimal point. Attempting to insert this value, however, does proceed, with the value being rounded to 0.57 to fit into the column. The column can hold the value, but with an accuracy of only two decimal digits. As to what your particular database system will do, in the case where you attempt to insert a value that does not conform to the column data type, you’ll just have to test it to make sure.

CREATE TABLE test_decimals
( 
  d   DECIMAL(3,2)  NOT NULL PRIMARY KEY
);

INSERT INTO test_decimals (d) VALUES (   9.37  );
INSERT INTO test_decimals (d) VALUES (   0.567 );
INSERT INTO test_decimals (d) VALUES (  12.34  );
INSERT INTO test_decimals (d) VALUES ( 888.88  );

SELECT
  d 
FROM
  test_decimals
;

 0.57
 9.37
12.34
99.99  

When using decimal data types, always choose a precision that comfortably holds the maximum range of data that the column is expected to contain. Make the scale adequate for your needs, too, considering that rounding will take place, especially where arithmetic calculations are performed.

For financial amounts, some people like to specify four decimal places instead of two for greater decimal accuracy, for example, interest calculations. Accuracy here refers to the decimal portion of the number; 12.0625 is more accurate than 12.06 if the number being represented is twelve and one sixteenth.

DECIMAL(p,s)
NUMERIC(p,s)

Example: Latitude and Longitude

Latitude and longitude (see Figure 9.1) are often expressed as decimals. Suppose we wanted to keep 6 positions to the right of the decimal point. The values we’re planning to store look like 43.697677 and -79.371643. Maybe that’s too accurate, because specifying 6 digits to the right of the decimal point corresponds to pinpointing a location on earth with a level of accuracy as refined as to the size of a grapefruit. To locate buildings, a scale of 4 (4 digits to the right of the decimal point) is sufficient.

Figure 9.1. Latitude and longitude

We could use DECIMAL(6,4) for latitude, which has values that range from –90° to +90°, but we’d need DECIMAL(7,4) for longitude, which has values that range from –180° to +180°.

Having seen the exact numeric data types—integer and decimal—let’s move on to the approximate numeric data types.

Approximate numbers are implemented as floating-point numbers, and are usually either very, very large, or very, very small. Floating-point numbers are often used for scientific data, where absolute accuracy is neither required nor assumed.

Consider this example of a very large number: a glass of water has approximately 7,900,000,000,000,000,000,000,000 water molecules. This number is much larger than a BIGINT column will allow. A decimal specification to hold this number would be DECIMAL(25,0) and that’s quite a large precision value—each digit will require extra storage space, but only two of the 25 digits are significant.

A floating-point number is compatible with scientific notation. That humongous number of water molecules can also be written as 7.9 x 1024, where 7.9 is called the mantissa and 24 is called the exponent. Scientific notation is useful because it separates the accuracy of the number from its largeness or smallness. Floating-point numbers also have a precision, but it applies to the mantissa only. Thus, 7.91 x 1024 is more accurate than 7.9 x 1024.

Why are floating-point numbers called approximate? Simply because of rounding errors, which depend in part on the underlying hardware architecture of the computer. A more detailed explanation is beyond the scope of this book; see Wikipedia’s page on IEEE Standard 754.

INSERT INTO test_floats 
  ( f ) 
VALUES 
  ( 7900000000000000000000000 )

INSERT INTO test_floats 
  ( f ) 
VALUES 
  ( 7.9E24 )

Whenever we perform a numeric calculation, data type conversion may occur depending on the calculation. A numeric calculation performed in an SQL statement will have a resulting value that’s typically given a data type large enough to accommodate it.

For example, the numbers 123,456,789 and 555 are perfectly good integers on their own, but if we multiply them together, the result is 68,518,517,895. This is also an integer, but will exceed an INTEGER column. If this multiplication is done in the SELECT clause of a query, the result is simply returned as a BIGINT data type. If we try to insert the result into an INTEGER column, we’ll receive an error message, as the value is too big.

As another example, suppose we multiply the two perfectly ordinary DECIMAL(3,2) numbers—1.23 and 4.56—together. The answer is 5.6088, and we know this value will be rounded if it’s stored into another DECIMAL(3,2) column. (An error would occur if the multiplication result is larger than 9.99.) If we simply return the calculation in a SELECT clause, the expression will have a data type of DECIMAL(5,4) or DECIMAL(6,4).

Now that we’ve learned how and when to assign the various numeric data types, let’s have a quick look at the functions that are available to work with them.

In the section called “Numeric Operators” in Chapter 7, we saw the basic arithmetic operators, +, -, *, and / (for addition, subtraction, multiplication, and division) used to combine numeric values when creating expressions in the SELECT clause. There are, in all database systems, a large number of additional numeric functions that can be used. Here are a few:

Database systems will vary according to which functions they offer. Some will have many more, including algebraic functions, geometric functions, and so on. Check your SQL reference manual for details.

Now let’s move on to the second of the three major data type categories: character.

The first of the three character data types is CHAR, also called CHARACTER, which is used to define a fixed-width character column. When specifying a CHAR column, we must give a width. Thus, CHAR(1) and CHAR(21) are examples of character data types resulting in columns which can hold character strings that are 1 and 21 characters long, respectively.

Again, the terminology here might be confusing. We speak of strings that have a length of so many characters, but we speak of character columns as being so many characters wide. This is mere convention, and both words should convey the same concept in this context.

If a character string value is inserted into a CHAR column with fewer characters than the column allows, it’s positioned at the left and padded with spaces on the right until it fills the column. Fortunately, we don’t have to specify those spaces when evaluating column values. Consider this condition:

WHERE country = 'Cuba'

If country is a CHAR(50) column, then the condition will evaluate as TRUE even though the value stored in the column consists of the letters Cuba followed by 46 spaces. Trailing spaces are ignored. All values in the column are 50 characters long.

If a character string value is inserted into a CHAR column with more characters than the column allows, excess characters are truncated from the right until it fits.

VARCHAR, also called VARYING CHARACTER, requires a width too, but in this case it’s the maximum width of the values allowed in the column. The actual width of the column value in each row depends on the value.

Thus, the value Cuba stored in a VARCHAR(50) column will require only 4 characters. This can make the rows substantially shorter. There is a very small amount of overhead required for VARCHAR columns—an additional number is required to indicate how long each value is. This number is typically a one byte binary number, which is why many database implementations allow VARCHAR widths only up to 255.

In Figure 9.2, the lengths for the VARCHAR values are actually stored in the row, whereas the lengths for the CHAR values are not.

Figure 9.2. How CHAR(50) and VARCHAR(50) are stored in the database

Check your SQL reference manual for the maximum column width allowed for both CHAR and VARCHAR columns.

The additional resource overhead for VARCHAR is typically more than offset by the fact that the rows are, in general, shorter. Thus, more rows can fit onto a single block of disk space, meaning that overall input and output operations performed by the database system will be faster for the same number of rows.

If there is variation in the lengths of a column’s values, my own preference is to use CHAR for widths up to 4, and VARCHAR for widths from 5 up, but that’s just a hunch as to where the break even point might be. The difference in row lengths with CHAR(4) versus VARCHAR(4) would be marginal, and I’ve yet to tested the differences in performance.

Of course, you can also have large CHAR columns if they’re appropriate. If all the values you wish to store are always the same length, then it only makes sense to use CHAR with a fixed width. Fixed length standard codes, like the EAN number used on product bar codes, are good candidates.

One of the few problems in deciding on the data type to use for a column is an edge case in which there’s a choice between numeric and character. Obviously, this choice is possible only when the values consist of digits and no other characters; as soon as any letter or symbol is involved, a numeric data type is out of the question.

The classic examples are phone numbers and American ZIP codes (postal codes). Neither contains any characters other than digits, so both appear to be good candidates for a numeric data type, especially given that:

These properties are often attractive to novices. Nevertheless, to paraphrase Einstein, not everything that can fit into a numeric column should be numeric. One good rule of thumb is: “if you’re not going to do arithmetic with it, leave it as a character data type.” Since finding an average phone number or the sum of all ZIP codes is a ridiculous proposition, numeric properties are not needed for these columns. The disadvantages of using numeric data types include formatting issues and sorting problems.

When formatting is required, a character data type is better. The phone number 9375551212 is a valid BIGINT value, but we’d rather see it formatted as (937) 555-1212. Doing this in every SELECT statement where we want to display the phone number becomes tedious. With a character data type, the formatting can be imposed upon data entry, storing the parentheses and dashes right in the value. Sorting the formatted values works too, since they would all have the same parentheses and dashes in the same positions.

If we store ZIP codes as a numeric data type, applying sorting would be disastrous. For ZIP codes, the presence of the optional last 4 digits—indicating a more specific segment within a delivery area—is problematic, since 5-digit and 9-digit codes would be in separate parts of the sorted results. Table 9.2 shows us that while sorting ZIP codes numerically is accurate for numbers, what we’d really prefer is alphabetical sorting.

As always, if you’re careful to analyse the needs of your application, you’ll be guided in your decision about which data type to use.

The SQL standard also provides for NATIONAL CHARACTER and NATIONAL CHARACTER VARYING data types. The NCHAR and NVARCHAR data types are just like CHAR and VARCHAR, but use a larger character set, most often one of the Unicode character sets such as UTF-8. You’d need this for internationalization purposes, for example, if you store characters from foreign languages.

These data types behave just like CHAR and VARCHAR, except the NCHAR and NVARCHAR columns require 2 bytes to store every character, rather than the 1 byte we’re used to with character sets like ASCII.

Consult your SQL reference manual for specifics about the character sets supported.

When the contents of a character column are expected to be relatively large, larger than can fit into the maximum CHAR or VARCHAR data type, a large-object character data type is required. In web development, this type of column is used to store things like the source text of an article entry (perhaps in plain text, perhaps with HTML or XML tags), the full description of a shopping cart item (as opposed to its shorter name or description), the content of a forum posting, and so on.

In standard SQL, the two data types CHARACTER LARGE OBJECT and BINARY LARGE OBJECT, also affectionately known as CLOB and BLOB, are provided for large column values. CLOBs are used to store character data (using character sets), while BLOBs are used to store binary data, such as images, sound, and video. Only some database systems implement both CLOBs and BLOBs, and these can vary in the way they’re implemented across systems; MySQL, for instance, implements TEXT instead of CLOB. Typical space limitations, if any, are in the megabyte range or even larger.

Both of these data types have restrictions in database systems that implement them. For example, CLOBs and BLOBs can’t be indexed or sorted—but then, doing so hardly makes sense.^[10] Imagine all the novels ever written stored in a large database; why would you want to list them sorted by their text content?

There are also some limitations with character functions and operators working with CLOBs or BLOBs. Speaking of which, it’s time for a quick review of character functions, more commonly known as string functions.

In Chapter 7, we saw two basic string functions, SUBSTRING and the concatenation operator (||), used to extract portions of, or combine character values when creating expressions in the SELECT clause. There are, in all database systems, a large number of additional string functions that can be used. Table 9.3 lists just a few.

It will vary amongst database systems who has what function, or even whether they use the same function name. There are many other useful string functions, particularly regular expressions, supported by some database systems, which allow for very sophisticated pattern matching. You know the drill by now: check your SQL reference manual for details of the string functions available to you.

Now let’s move on to the third major data type category: temporal.

The DATE data type is used to store date values from the Common Era calendar, which is the standard, 365-day Gregorian calendar. A date value has three components: year, month, and day. An example of a date constant can be seen in the following query:

SELECT
  customer_id
FROM
  carts
WHERE
  cartdate = DATE '2008-09-21'

The important point to note here is that, in the above query (and in the SQL standard), the keyword DATE is actually part of the date constant value. However, because most database systems were developed before dates and times were standardized in SQL, standard SQL doesn’t always work. For example, the above query works fine in MySQL, but fails in SQL Server until the DATE keyword is removed:

SELECT
  customer_id
FROM
  carts
WHERE
  cartdate = '2008-09-21'

This form of the date constant looks like a simple character string value, and will work in all database systems, so I recommend using it.

The next issue with dates is even more problematic for novices.

DATE '2008-09-21'
'2008-09-21' 
'20080921' 
20080921 
'09-21-2008' 
'21-SEP-2008' 
'2008/09/21' 
'2008$09$21'

Time values are similar to date values, in that there are differences between input format, storage format, and display format. An input time format might look like TIME '09:37' and a display format might look like 9:37 AM. Internally, time values are often stored as another integer, representing the number of clock ticks after midnight. (A clock tick might be a millisecond, or three milliseconds, or some similar value.)

Times, however, have another aspect that makes them a bit trickier. That’s because adding dates together is pure folly, yet adding times can make sense.

swim 20:35
bike 1:49:59
run 1:28:32

SELECT
  SEC_TO_TIME( SUM( TIME_TO_SEC(splittime) ) ) AS total_time
FROM
  raceresults

Timestamps are data types that contain both a date and a time component. What we’ve learned about dates and times separately applies equally to dates and times combined in timestamps: be careful with input formats, and reformat for display in the application if necessary.

event_date   DATE
event_start  TIME
event_end    TIME

event_start  TIMESTAMP
event_end    TIMESTAMP

Intervals are like the time duration examples we saw earlier in the athletic race: swim 20:35, bike 1:49:59, and run 1:28:32. Naturally, there are date intervals as well. The interval including January 1st through to March 1st is either 59 or 60 days, depending on the year.

In standard SQL, intervals have their own special syntax. However, few database systems have adopted the standard interval syntax, primarily because—as stated previously—the need for date calculations was anticipated by every database system, and implemented as date functions, long before the standard was agreed to.

We saw one example of an interval calculation back in the section called “BETWEEN: It haz a flavr” in Chapter 4:

CURRENT_DATE - INTERVAL 5 DAY

This is an expression in standard SQL that calculates the date that is 5 days earlier than the current date. If it fails to work in your particular database system, there’ll be equivalent date functions for the same purpose. As we mentioned back in the section called “Temporal Operators” in Chapter 7, you’ll have to read the documentation for your system.

Database system implementations have a rich variety of date functions. We call them date functions, but they also include time functions, and timestamp functions. Standard SQL has few date functions, EXTRACT being the main one, in addition to functions that perform interval calculations. Standard SQL also has the three functions, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP, designed expressly to return the corresponding date and time value from the computer that database system is running on.

Each database system also has a number of other non-standard date functions. Some, such as WEEKDAY, are decidedly useful in real world applications. Table 9.4 lists some of the date functions available in one database system or another.

Date functions are, in general, very comprehensive, but it’s important to use them correctly. Refer to your SQL reference manual for more details.

The first constraint that we should think about for any column, is whether the column should allow NULLs. Any attempt to insert a row in which a value is missing for a column designated as NOT NULL will fail, and the database system will return an error message.

How do we decide if a column should be NULL or NOT NULL? Simply, if we need to have a value in every possible instance. For example, it’s impossible to have an item on a customer cart without a selected quantity:

CREATE TABLE cartitems
(
  cart_id  INTEGER  NOT NULL
, item_id  INTEGER  NOT NULL
, qty      SMALLINT NOT NULL
);

The qty column is NOT NULL because it’s senseless to have a customer cart for a null quantity of an item. Key columns, like cart_id and item_id, must also be NOT NULL, but we’ll cover them in Chapter 10.

The DEFAULT constraint allows us to specify a default value for a column. This default value will be used in those instances where a NULL is about to be inserted. Let’s adjust the cartitems table so that the default qty value for any item is 1:

CREATE TABLE cartitems
(
  cart_id  INTEGER  NOT NULL
, item_id  INTEGER  NOT NULL
, qty      SMALLINT NOT NULL  DEFAULT 1
);

We’ve also used a DEFAULT constraint in our customers table for the shipping address:

CREATE TABLE customers
(
  id        INTEGER      NOT NULL PRIMARY KEY
, name      VARCHAR(99)  NOT NULL
, billaddr  VARCHAR(255) NOT NULL
, shipaddr  VARCHAR(255) NOT NULL DEFAULT 'See billing address.'
);

The default for the shipping address is the constant ‘See billing address’, and this string would be inserted into the shipaddr column when a customer is added to the table without specifying a shipping address.

CHECK constraints are even more useful, because they can be as complex as needed by the application. A CHECK constraint consists of the keyword CHECK followed by a parenthesized condition. The neat part is that this condition can be a compound condition, involving AND and OR, just like in the WHERE clause.

Lets adjust the CREATE query for the cartitems table so that the maximum qty value for any item is 10:

CREATE TABLE cartitems
(
  cart_id  INTEGER  NOT NULL
, item_id  INTEGER  NOT NULL
, qty      SMALLINT NOT NULL  DEFAULT 1  CHECK ( qty <= 10 )
);

In our forums application, the CHECK constraint was used to ensure the TIMESTAMP value in the revised column was always after (chronologically speaking) the value in the created column:

  created  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP
, revised  TIMESTAMP  NULL      CHECK ( revised >= created )