Column Data Types

In SQL, you store different types of information. You can see that in the example table that we are creating. For most of the items, the type of information being stored is a character-based string of letters and numbers. That is, it supports the letters of the alphabet, numbers, and so on. It's beyond the scope of this course to get into all the different data types available to you, but for the purposes of this module, you will need to understand that you may want to use the following basic data types:

1. CHAR(n) specifies that the information is a character type. In the parentheses, specify the maximum number of characters that you will allow in the column.
2. NUMERIC (precision, scale) indicates that the column will hold number values. The precision refers to the number of digits you want to store, and the scale indicates how many of those digits will be used for decimal values, those to the right of the decimal point. For example, if you want to use the column for money, you might indicate NUMERIC(10,2) for a field that will support 10 total digits, eight to the left and two to the right of the decimal place.
3. INT allows only whole numbers. For example, if you have a value of 1.2, the integer representation of this is 1.

A key part of a column's definitions is the data type. The following list summarizes the most common SQL data types:

1. BLOB: A Binary Large Object. This is any chunk of binary data such as a JPEG file, audio file, video file, or Word document. The database knows nothing about the internal structure of this data so, for example, if the BLOB contains a Word document the database cannot search its contents.
2. BOOLEAN: A true or false value.
3. CHAR: A fixed-length string. Use this for strings that always have the same length such as two-letter state abbreviations or five-digit ZIP Codes.
4. DATE: A month, date, and year such as February 29, 2012.
5. DATETIME: A date and time such as 12:34pm February 29, 2012.
6. DECIMAL(p, s): A fixed-point number where p (precision) gives the total number of digits and s (scale) gives the number of digits to the right of the decimal. For example, DECIMAL(6, 2) holds numbers of the form 1234.56.
7. INT: An integer value.
8. NUMBER: A floating point number.
9. TIME: A time without a date such as 3:14am.
10. TIMESTAMP: A date and time.
11. VARCHAR: A variable-length string. Use this for strings of unknown lengths such as names and street addresses.

Specific database products often provide extra data types and aliases for these types. They also sometimes use these names for different purposes. For example, in different databases the INT data type might use 32 or 64 bits, and the database may provide other data types such as SMALLINT, TINYINT, BIGINT, and so forth to hold integers of different sizes.

In general, all the popular database servers have the capacity to store the same types of data, such as strings, dates, and numbers. Where they typically differ is in the specialty data types, such as XML documents or very large text or binary documents. Since this is an introductory book on SQL, and since 98% of the columns you encounter will be simple data types, this book covers only the character, date, and numeric data types.

Character data can be stored as either fixed-length or variable-length strings; the difference is that fixed-length strings are right-padded with spaces and always consume the same number of bytes, and variable-length strings are not right-padded with spaces and don't always consume the same number of bytes. When defining a character column, you must specify the maximum size of any string to be stored in the column. For example, if you want to store strings up to 20 characters in length, you could use either of the following definitions:

char(20) /* fixed-length */
varchar(20) /* variable-length */

The maximum length for char columns is currently 255 bytes, whereas varchar columns can be up to 65,535 bytes. If you need to store longer strings (such as emails, XML documents, etc.), then you will want to use one of the text types (mediumtext and longtext), which I cover later in this section. In general, you should use the char type when all strings to be stored in the column are of the same length, such as state abbreviations, and the varchar type when strings to be stored in the column are of varying lengths. Both char and varchar are used in a similar fashion in all the major database servers.

Create Table - Exercise

SQL Database Programming