<h3>
SQL Data Types</h3>The ANSI/ISO SQL standard specifies the various types of data that can be stored in a SQL-based database and manipulated by the SQL language. The original SQL1 standard specified only a minimal set of data types. The SQL2 standard expanded this list to include variable-length character strings, date and time data, bit strings, and other types. Today's commercial DBMS products can process a rich variety of different kinds of data, and there is considerable diversity in the particular data types supported across different DBMS brands. Typical data types include:
•
Integers: Columns holding this type of data typically store counts, quantities, ages, and so on. Integer columns are also frequently used to contain I.D. numbers, such as customer, employee, and order numbers.
•
Decimal numbers: Columns with this data type store numbers that have fractional parts and must be calculated exactly, such as rates and percentages. They are also frequently used to store money amounts.
•
Floating point numbers: Columns with this data type are used to store scientific numbers that can be calculated approximately, such as weights and distances. Floating point numbers can represent a larger range of values than decimal numbers but can produce round-off errors in computations.
•
Fixed-length character strings: Columns holding this type of data typically store names of people and companies, addresses, descriptions, and so on.
•
Variable-length character strings: This data type allows a column to store character strings that vary in length from row to row, up to some maximum length. (The SQL1 standard permitted only fixed-length character strings, which are easier for the DBMS to process but can waste considerable space.)
•
Money amounts: Many SQL products support a MONEY or CURRENCY type, which is usually stored as a decimal or floating point number. Having a distinct money type allows the DBMS to properly format money amounts when they are displayed.
•
Dates and times: Support for date/time values is also common in SQL products, although the details vary dramatically from one product to another. Various combinations of dates, times, timestamps, time intervals, and date/time arithmetic are generally supported. The SQL2 standard includes an elaborate specification for DATE, TIME, TIMESTAMP, and INTERVAL data types, including support for time zones and time precision (for example, tenths or hundredths of seconds).
•
Boolean data: Some SQL products, such as Informix Dynamic Server, support logical (TRUE or FALSE) values as an explicit type, and some permit logical operations (comparison, AND/OR, and so on) on the stored data within SQL statements.
•
Long text: Several SQL-based databases support columns that store long text strings (typically up to 32,000 or 65,000 characters, and in some cases even larger). This allows the database to store entire documents, product descriptions, technical papers, resumes, and similar unstructured text data. The DBMS usually restricts the use of these columns in interactive queries and searches.
•
Unstructured byte streams: Several DBMS products allow unstructured, variablelength sequences of bytes to be stored and retrieved. Columns containing this data are used to store compressed video images, executable code, and other types of unstructured data. SQL Server's IMAGE data type, for example, can store a stream of up to 2 billion bytes of data.
•
Asian characters: As databases grow to support global applications, DBMS vendors have added support for fixed-length and variable-length strings of 16-bit characters used to represent Kanji and other Asian characters. Searching and sorting on these GRAPHIC and VARGRAPHIC types is usually not permitted, however.
