Understanding SQL Data Types

SQL (Structured Query Language) defines a rich set of data types that allow you to store various kinds of information in your database tables. Choosing the right data type is crucial for data integrity, efficiency, and performance.

Numeric Types

These types are used to store numbers. They vary in precision and range.

Exact Numeric Types

Type Description Storage Example
INT or INTEGER Standard integer, typically 32-bit. 4 bytes 12345
BIGINT Larger integer, typically 64-bit. 8 bytes 123456789012345
SMALLINT Smaller integer, typically 16-bit. 2 bytes 32767
DECIMAL(p, s) or NUMERIC(p, s) Fixed-point number with precision p (total digits) and scale s (digits after decimal). Variable DECIMAL(10, 2) for 12345678.90
BIT(n) Fixed-length bit string of length n. Varies BIT(8) for '10110010'

Approximate Numeric Types

Type Description Storage Example
FLOAT(p) Approximate floating-point number. p specifies minimum precision in bits. 4 or 8 bytes 3.14159
REAL Alias for FLOAT(p) with a single-precision requirement. 4 bytes 2.71828
DOUBLE PRECISION Alias for FLOAT(p) with a double-precision requirement. 8 bytes 1.6180339887

String/Text Types

These types are used for storing character data.

Type Description Storage Example
CHAR(n) Fixed-length string of n characters. Padded with spaces if shorter. n characters 'SQL ' (if n=4)
VARCHAR(n) Variable-length string with a maximum length of n characters. length + 1-2 bytes 'Database'
TEXT Variable-length string for large amounts of text. Varies (typically large) 'This is a very long description...'
NCHAR(n) Fixed-length national character string (supports Unicode). n * max_bytes_per_char '你好'
NVARCHAR(n) Variable-length national character string (supports Unicode). Varies 'Ελληνικά'

Date and Time Types

Store date and time information.

Type Description Format Example
DATE Stores a date (year, month, day). 'YYYY-MM-DD' (e.g., '2023-10-27')
TIME Stores a time (hour, minute, second, optional fractional seconds). 'HH:MM:SS.ffffff' (e.g., '14:30:00.123')
DATETIME Stores a combination of date and time. 'YYYY-MM-DD HH:MM:SS.ffffff' (e.g., '2023-10-27 14:30:00')
TIMESTAMP Stores a date and time, often with time zone information or auto-updating capabilities. 'YYYY-MM-DD HH:MM:SS.ffffff'
YEAR Stores a year. YYYY (e.g., 2023)

Boolean Type

Represents truth values.

Type Description Values
BOOLEAN or BOOL Stores a truth value. TRUE, FALSE (often represented internally as 1 and 0)
Example: A column named is_active of type BOOLEAN could store TRUE for active users and FALSE for inactive ones.

Binary Types

Used for storing raw binary data.

Type Description Example
BINARY(n) Fixed-length binary string. Padded with 0x00 bytes. '0xFF00' (if n=2)
VARBINARY(n) Variable-length binary string. 0x1A2B3C
BLOB (Binary Large Object) For storing large binary data like images or files. Image file data

Other Important Types

Type Description Example
JSON Stores JSON documents. '{"name": "Alice", "age": 30}'
UUID Universally Unique Identifier. 'a1b2c3d4-e5f6-7890-1234-567890abcdef'
ENUM A string object whose value must be one of a list of allowed values. ENUM('Pending', 'Processing', 'Completed')
SET A string object whose value can be zero or more of a list of allowed values. SET('Read', 'Write')

Different database systems (like MySQL, PostgreSQL, SQL Server, Oracle) might have slightly different names, implementations, and storage sizes for these data types. Always refer to the specific documentation for your database system.