SQL data types make it easy to specify data in a particular column of the table more accurately. You can set a column to store only dates, only characters, etc.
The data type that you set enables SQL to identify how to interact with that part of data. Data types in SQL vary due to different database systems and slight differences in the syntax used. You should always check with the specific database documentation to see whether the data type you're planning to set is supported.
Contents
SQL Data Types: Main Tips
- A data type describes the type of value to store in a column.
- Each database column must have data type and name specified – this needs to be done before an SQL table can be created.
- Data types help SQL interact with column data properly.
- Different databases offer different data type definitions.
- Data types might not act the same when used on different databases, regardless of their names being the same.
General Data Types
| Data type | Description |
|---|---|
| CHARACTER(n) | Fixed-length n. Character string. |
| CHARACTER VARYING(n) or VARCHAR(n) | Character string. Maximum length is n. Variable length. |
| BINARY(n) | Fixed-length n. Binary string. |
| BOOLEAN | Stores FALSE or TRUE values. |
| BINARY VARYING(n) or VARBINARY(n) | Binary string. Maximum length n. Variable length. |
| INTEGER(p) | Precision p. No decimal (integer numerica). |
| SMALLINT | Precision 5. No decimal (integer numerica). |
| INTEGER | Precision 10. No decimal (integer numerica). |
| BIGINT | Precision 19. No decimal (integer numerica). |
| DECIMAL(p,s) | Exact numerical, scale s., precision p, scale s. Example: decimal(6,2) is a number that has 4 digits before the decimal and 3 digits after the decimal. |
| NUMERIC(p,s) | Same as DECIMAL. Exact numerical, scale s., precision p, scale s. |
| FLOAT(p) | Approximate numerical, mantissa precision p. The size argument consists of the minimum precision number. |
| REAL | Approximate numerical, mantissa precision 7. |
| FLOAT | Approximate numerical, mantissa precision 16. |
| DOUBLE PRECISION | Approximate numerical, mantissa precision 16. |
| DATE | Stores day, month, and year values. |
| TIME | Stores second, minute, and hour values. |
| TIMESTAMP | Stores second, minute, hour, day, month, year values. |
| INTERVAL | Composed of a number of integer fields, representing a period of time, depending on the interval type. |
| ARRAY | An ordered collection of elements and a set-length. |
| MULTISET | An unordered collection of elements and a variable-length. |
| XML | XML data is storage. |
Quick Reference to SQL Data Types
In this table, you see some of the most popular data type names in different database platforms:
| Data type | Access | SQLServer | Oracle | MySQL | PostgreSQL |
|---|---|---|---|---|---|
| boolean | Yes/No | Bit | Byte | N/A | Boolean |
| integer | Number (integer) | Int | Number | Int Integer |
Int Integer |
| float | Number (single) | Float Real |
Number | Float | Numeric |
| currency | Currency | Money | N/A | N/A | Money |
| string (fixed) | N/A | Char | Char | Char | Char |
| string (variable) | Text (<256) Memo (65k+) |
Varchar | Varchar Varchar2 |
Varchar | Varchar |
| binary object | OLE Object Memo | Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB) |
Long Raw |
Blob Text |
Binary Varbinary |
SQL Data Types: Summary
- Data types describe the type of data stored in a particular column.
- Prior to creating an SQL table, you must specify each column data type and name.
- Data types make it very easy to interact with data in certain columns.
- There are different data type definitions for different databases.
- Despite having identical names, data types might work differently on different databases.