SQL Data Types
The International Organization for Standardization (ISO) SQL standard specifies the various types of data that can be stored in a SQL-based database and manipulated by the SQL language. 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:
- Integer: 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 student, employee, and others.
INTEGER, INT, and SMALLINT
- Decimal: The DECIMAL data type is an exact number with a fixed precision and scale. Precision is an integer representing the total number of digits allowed in a column. Scale is also an integer value that represents the number of decimal places. 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: Columns with this data type are used to store scientific numbers that can be calculated approximately, such as weights and distances. Floating can represent a larger range of values than decimal numbers but can produce round-off errors in computations.
- Fixed-length character string: Columns holding this type of information ordinarily store names of people and companies, addresses, descriptions, etc.
CHAR (len) and CHARACTER (len)
- Variable-length character string: This data type allows a column to store character strings that vary in length from row to row, up to some maximum length.
VARCHAR (len), CHAR VARYING (len), CHARACTER VARYING (len)
- Dates and time: 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.
- Boolean: 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 stream: Several DBMS products allow unstructured, variable-length 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 character: 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 these GRAPHIC and VARGRAPHIC types is usually not permitted, however.
Leave a comment
No Cmomments yet