SQL Data Types: MySQL, SQL Server, and MS Access

Understanding SQL data types is crucial for designing efficient and reliable databases. The data type of a column determines what kind of values it can store (integers, text, dates, etc.). Choosing the right data type is essential for data integrity and database performance.

Data Types: An Overview

Different database systems (MySQL, SQL Server, MS Access) have their own sets of data types, and even when data types have the same name, their specific characteristics (size limits, allowed values, etc.) might vary. Always refer to the documentation for your specific database system.

Key Data Type Categories

  • String: For storing text (letters, numbers, special characters).
  • Numeric: For storing numbers (integers, decimals, floating-point numbers).
  • Date and Time: For storing dates and times.
  • Binary: For storing binary data.

MySQL Data Types (Version 8.0)

String Data Types

Data Type Description Maximum Length
CHAR(size) Fixed-length string. 255 characters
VARCHAR(size) Variable-length string. 65,535 characters
BINARY(size) Fixed-length binary string. 255 bytes
VARBINARY(size) Variable-length binary string. 65,535 bytes
TINYBLOB BLOB (Binary Large Object), max 255 bytes. 255 bytes
TINYTEXT Text string, max 255 characters. 255 characters
TEXT(size) Text string. 65,535 bytes
BLOB(size) BLOB (Binary Large Object). 65,535 bytes
MEDIUMTEXT Text string, max 16,777,215 characters. 16,777,215 characters
MEDIUMBLOB BLOB, max 16,777,215 bytes. 16,777,215 bytes
LONGTEXT Text string, max 4,294,967,295 characters. 4,294,967,295 characters
LONGBLOB BLOB, max 4,294,967,295 bytes. 4,294,967,295 bytes
ENUM(val1, val2, ...) String that can only be one of the listed values. 65,535 values
SET(val1, val2, ...) String that can be one or more of the listed values. 64 values

Numeric Data Types

Data Type Description Signed Range Unsigned Range
BIT(size) Bit-value type. - -
TINYINT(size) Very small integer. -128 to 127 0 to 255
BOOL/BOOLEAN Boolean (true/false). - -
SMALLINT(size) Small integer. -32,768 to 32,767 0 to 65,535
MEDIUMINT(size) Medium integer. -8,388,608 to 8,388,607 0 to 16,777,215
INT(size)/INTEGER(size) Integer. -2,147,483,648 to 2,147,483,647 0 to 4,294,967,295
BIGINT(size) Large integer. -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18,446,744,073,709,551,615
FLOAT(size,d)/FLOAT(p) Floating-point number. - -
DOUBLE(size,d)/DOUBLE PRECISION(size,d) Double-precision floating-point number. - -
DECIMAL(size,d)/DEC(size,d) Exact fixed-point number. - -

All numeric types can be UNSIGNED or ZEROFILL. UNSIGNED disallows negative values; ZEROFILL adds leading zeros and implies UNSIGNED.

Date and Time Data Types

Data Type Description Format Range
DATE Date. YYYY-MM-DD 1000-01-01 to 9999-12-31
DATETIME(fsp) Date and time. YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP(fsp) Timestamp (seconds since Unix epoch). YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC to 2038-01-09 03:14:07 UTC
TIME(fsp) Time. HH:MM:SS -838:59:59 to 838:59:59
YEAR Year (4-digit). YYYY 1901 to 2155, and 0000

SQL Server Data Types

String Data Types

Data Type Description Max Size Storage
char(n) Fixed-width character string. 8,000 characters Defined width
varchar(n) Variable-width character string. 8,000 characters 2 bytes + number of chars
varchar(max) Variable-width character string. 2GB 2 bytes + number of chars
text Variable-width character string. 2GB 4 bytes + number of chars
nchar(n) Fixed-width Unicode string. 4,000 characters Defined width x 2
nvarchar(n) Variable-width Unicode string. 4,000 characters 2 bytes + number of chars x 2
nvarchar(max) Variable-width Unicode string. 2GB 2 bytes + number of chars x 2
ntext Variable-width Unicode string. 2GB 4 bytes + number of chars x 2
binary(n) Fixed-width binary string. 8,000 bytes -
varbinary(n) Variable-width binary string. 8,000 bytes -
varbinary(max) Variable-width binary string. 2GB -
image Variable-width binary string. 2GB -

Numeric Data Types

Data Type Description Storage
bit Integer (0, 1, or NULL). 1 bit
tinyint Small integer. 1 byte
smallint Small integer. 2 bytes
int Integer. 4 bytes
bigint Large integer. 8 bytes
decimal(p,s)/numeric(p,s) Fixed precision and scale numbers. 5-17 bytes

For `decimal` and `numeric`, `p` is the total number of digits (1-38), and `s` is the number of digits after the decimal point (0-p).

MS Access Data Types

(Include a table here summarizing the data types in MS Access)

**Note:** You'll need to populate the MS Access data types table with the appropriate information. Storage sizes are approximate and can vary depending on the database system and specific settings. This table provides a general overview; always refer to the official documentation for your specific database system for the most accurate details. SQL Data Types: MySQL, SQL Server, and MS Access

SQL Data Types: MySQL, SQL Server, and MS Access

Understanding SQL data types is crucial for designing efficient and reliable databases. The data type of a column determines what kind of values it can hold (integers, text, dates, etc.). Choosing the right data type ensures data integrity and optimizes database performance. Different database systems (MySQL, SQL Server, MS Access) have their own sets of data types, and even when data types share the same name, their specific characteristics (size limits, allowed values, etc.) can vary significantly. Always refer to the documentation for your specific database system.

MySQL Data Types (Version 8.0)

String Data Types

Data Type Description Maximum Length
CHAR(size) Fixed-length string (letters, numbers, special characters). 255 characters
VARCHAR(size) Variable-length string. 65,535 characters
BINARY(size) Fixed-length binary string. 255 bytes
VARBINARY(size) Variable-length binary string. 65,535 bytes
TINYBLOB BLOB (Binary Large Object), max 255 bytes. 255 bytes
TINYTEXT Text string, max 255 characters. 255 characters
TEXT(size) Text string. 65,535 bytes
BLOB(size) BLOB (Binary Large Object). 65,535 bytes
MEDIUMTEXT Text string, max 16,777,215 characters. 16,777,215 characters
MEDIUMBLOB BLOB, max 16,777,215 bytes. 16,777,215 bytes
LONGTEXT Text string, max 4,294,967,295 characters. 4,294,967,295 characters
LONGBLOB BLOB, max 4,294,967,295 bytes. 4,294,967,295 bytes
ENUM(val1, val2, ...) String with a value from a defined list. 65,535 values
SET(val1, val2, ...) String with zero or more values from a defined list. 64 values

Numeric Data Types

Data Type Description Signed Range Unsigned Range
BIT(size) Bit-value type. - -
TINYINT(size) Very small integer. -128 to 127 0 to 255
BOOL/BOOLEAN Boolean (true/false). - -
SMALLINT(size) Small integer. -32,768 to 32,767 0 to 65,535
MEDIUMINT(size) Medium integer. -8,388,608 to 8,388,607 0 to 16,777,215
INT(size)/INTEGER(size) Integer. -2,147,483,648 to 2,147,483,647 0 to 4,294,967,295
BIGINT(size) Large integer. -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18,446,744,073,709,551,615
FLOAT(size,d)/FLOAT(p) Floating-point number. - -
DOUBLE(size,d)/DOUBLE PRECISION(size,d) Double-precision floating-point number. - -
DECIMAL(size,d)/DEC(size,d) Exact fixed-point number. - -

Numeric types can be UNSIGNED (no negatives) or ZEROFILL (adds leading zeros and implies UNSIGNED).

Date and Time Data Types

Data Type Description Format Range
DATE Date. YYYY-MM-DD 1000-01-01 to 9999-12-31
DATETIME(fsp) Date and time. YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP(fsp) Timestamp (seconds since Unix epoch). YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC to 2038-01-09 03:14:07 UTC
TIME(fsp) Time. HH:MM:SS -838:59:59 to 838:59:59
YEAR Year (4-digit). YYYY 1901 to 2155, and 0000

SQL Server Data Types

String Data Types

Data Type Description Max Size Storage
char(n) Fixed-width character string. 8,000 characters Defined width
varchar(n) Variable-width character string. 8,000 characters 2 bytes + number of chars
varchar(max) Variable-width character string. 2GB 2 bytes + number of chars
text Variable-width character string. 2GB 4 bytes + number of chars
nchar(n) Fixed-width Unicode string. 4,000 characters Defined width x 2
nvarchar(n) Variable-width Unicode string. 4,000 characters 2 bytes + number of chars x 2
nvarchar(max) Variable-width Unicode string. 2GB 2 bytes + number of chars x 2
ntext Variable-width Unicode string. 2GB 4 bytes + number of chars x 2
binary(n) Fixed-width binary string. 8,000 bytes -
varbinary(n) Variable-width binary string. 8,000 bytes -
varbinary(max) Variable-width binary string. 2GB -
image Variable-width binary string. 2GB -

Numeric Data Types

Data Type Description Storage
bit Integer (0, 1, or NULL). 1 bit
tinyint Small integer. 1 byte
smallint Small integer. 2 bytes
int Integer. 4 bytes
bigint Large integer. 8 bytes
decimal(p,s)/numeric(p,s) Fixed precision and scale numbers. 5-17 bytes
smallmoney Monetary data. 4 bytes
money Monetary data. 8 bytes
float(n) Floating precision number. 4 or 8 bytes
real Floating precision number. 4 bytes

Date and Time Data Types

Data Type Description Storage
datetime Date and time. 8 bytes
datetime2 Date and time (higher precision). 6-8 bytes
smalldatetime Date and time (lower precision). 4 bytes
date Date only. 3 bytes
time Time only. 3-5 bytes
datetimeoffset Date and time with time zone offset. 8-10 bytes
timestamp Unique number updated on row creation/modification. 8 bytes

Other Data Types

Data Type Description
sql_variant Can store various data types.
uniqueidentifier Globally unique identifier (GUID).
xml XML data (max 2GB).
cursor Cursor reference.
table Result-set for processing.

MS Access Data Types

Data Type Description Storage
Text Text (up to 255 characters). -
Memo Long text (up to 65,536 characters). -
Byte Integer (0-255). 1 byte
Integer Integer (-32,768 to 32,767). 2 bytes
Long Long integer. 4 bytes
Single Single-precision floating-point. 4 bytes
Double Double-precision floating-point. 8 bytes
Currency Monetary value. 8 bytes
AutoNumber Automatically incremented number. 4 bytes
Date/Time Date and time value. 8 bytes
Yes/No Boolean (True/-1 or False/0). 1 bit
OLE Object Binary Large Object (BLOB). Up to 1GB
Hyperlink Hyperlink. 4 bytes
Lookup Wizard Dropdown list. 4 bytes
**Note:** Storage sizes are approximate and can vary based on the database system and its settings. This is a general overview; consult your specific database system's documentation for precise details. Remember to replace the bracketed `(...)` comments with the actual data types for MS Access.