sql_datatypes_general

SQL General Data Types

A data type defines the kind of value a column can hold, such as integer, character string, date/time, or boolean. It also determines the operations that can be performed on that data.

SQL General Data Types Overview

Every column in a database table must have a name and a data type. When creating a table, SQL developers decide what type of data will be stored in each column.

The data type acts as a guideline for SQL to understand the nature of the data within each column, how it should be stored, and how SQL should interact with it (e.g., for comparisons, calculations, or sorting).

The following table lists some general SQL data type categories and common standard types. Note that specific implementations and names can vary between database systems.

General Data Type Category / ANSI SQL Type	Description
CHAR(n)	Fixed-length character string of length n.
VARCHAR(n)	Variable-length character string with a maximum length of n.
TEXT / CLOB	Variable-length character string for large text data. (Names vary: TEXT, CLOB, VARCHAR(MAX))
NCHAR(n), NVARCHAR(n), NTEXT	National character versions for Unicode strings (names and availability vary).
BINARY(n)	Fixed-length binary string of length n.
VARBINARY(n)	Variable-length binary string with a maximum length of n.
BLOB / BYTEA	Variable-length binary data for large objects like images or files. (Names vary: BLOB, BYTEA, VARBINARY(MAX), IMAGE (deprecated))
BOOLEAN	Stores TRUE or FALSE values. (Implementation varies: BIT, TINYINT(1), native BOOLEAN)
INTEGER / INT	Integer numerical data (whole numbers). Precision varies (e.g., SMALLINT, INTEGER, BIGINT).
DECIMAL(p,s) / NUMERIC(p,s)	Exact numerical data with precision p (total number of digits) and scale s (digits after the decimal point). E.g., DECIMAL(10,2) can store numbers like 12345678.90.
FLOAT(p) / REAL / DOUBLE PRECISION	Approximate numerical data (floating-point numbers). Precision varies.
DATE	Stores date values (year, month, day).
TIME	Stores time values (hour, minute, second, fractional seconds). May include time zone.
TIMESTAMP / DATETIME	Stores both date and time values. May include time zone and fractional seconds. (DATETIME, DATETIME2, TIMESTAMP WITH/WITHOUT TIME ZONE)
INTERVAL	Represents a period of time.
XML	Stores XML data.
JSON	Stores JSON data (increasingly common in modern databases).
ARRAY	An ordered collection of elements of the same type (not universally supported).
UUID / GUID	Universally Unique Identifier (supported in many systems).

SQL Data Type Quick Reference Across Platforms

Different database systems often have their own names and specific characteristics for data types, even for conceptually similar types. The table below shows common mappings for some frequently used data types. This is a general guide; always consult the official documentation for your specific RDBMS.

Common Data Type Mappings (Illustrative):

Category         | MS Access                | SQL Server                             | Oracle                                  | MySQL                                    | PostgreSQL
-----------------|--------------------------|----------------------------------------|-----------------------------------------|------------------------------------------|---------------------------------
Boolean          | Yes/No                   | BIT                                    | (Use NUMBER(1) or CHAR(1))              | BOOL, BOOLEAN (aliases for TINYINT(1))   | BOOLEAN
Integer          | Number (Integer, Long Int)| INT, BIGINT, SMALLINT, TINYINT       | NUMBER(p,0), INTEGER                   | INT, BIGINT, SMALLINT, TINYINT           | INTEGER, BIGINT, SMALLINT
Exact Decimal    | Number (Decimal)         | DECIMAL(p,s), NUMERIC(p,s)             | NUMBER(p,s)                             | DECIMAL(p,s), NUMERIC(p,s)               | NUMERIC(p,s), DECIMAL(p,s)
Approx. Float    | Number (Single, Double)  | FLOAT, REAL                            | BINARY_FLOAT, BINARY_DOUBLE             | FLOAT, DOUBLE                            | REAL, DOUBLE PRECISION
Currency         | Currency                 | MONEY, SMALLMONEY                      | (Use NUMBER(p,s))                       | (Use DECIMAL(p,s))                       | MONEY (fixed precision)
Fixed String     | (Use Short Text)         | CHAR(n), NCHAR(n)                      | CHAR(n), NCHAR(n)                       | CHAR(n)                                  | CHARACTER(n), CHAR(n)
Variable String  | Short Text (<256), Long Text | VARCHAR(n), NVARCHAR(n), VARCHAR(MAX), NVARCHAR(MAX) | VARCHAR2(n), NVARCHAR2(n), CLOB, NCLOB | VARCHAR(n), TEXT (and variants)          | VARCHAR(n), TEXT, CHARACTER VARYING(n)
Date Only        | Date/Time (format as Date)| DATE                                   | DATE (includes time, or use TRUNC)      | DATE                                     | DATE
Date and Time    | Date/Time                | DATETIME2, DATETIME, SMALLDATETIME, DATETIMEOFFSET | DATE (always includes time), TIMESTAMP (variants) | DATETIME, TIMESTAMP                      | TIMESTAMP (variants), TIMESTAMPTZ
Binary Large Obj | OLE Object (old), Attachment | VARBINARY(MAX), IMAGE (deprecated)   | BLOB, BFILE                             | BLOB (and variants like LONGBLOB)        | BYTEA

	Crucial Note: Data type names, supported sizes, precision, and behavior can differ significantly between database systems. Always refer to the official documentation for the RDBMS you are using for accurate and up-to-date information.