Code has been added to clipboard!

MySQL, Microsoft Access and SQL Server Data Types

Reading time 6 min
Published Aug 9, 2017
Updated Oct 3, 2019

TL;DR – Different database management systems use different server data types, so it's vital to always use relevant ones.

SQL Server Data Types

SQL Server String Data Types

Type Description Storage
char(n) A character string of a defined width Up to 8,000 characters
varchar(n) A variable string of a defined width Up to 8,000 characters.
varchar(max) A variable string of a defined width Up to 1,073,741,824 characters
text A variable string of a defined width Up to 2GB data of text
nchar A Unicode string of a defined width Up to 4,000 chars
nvarchar A Unicode string of a variable width Up to 4,000 chars
nvarchar(max) A Unicode string of a variable width Up to 536,870,912 chars
ntext A Unicode string of a variable width Up to 2GB text data
binary(n) A binary string of a defined width Up to 8,000 bytes
varbinary A binary string of a variable width Up to 8,000 bytes
varbinary(max) A binary string of a variable width Up to 2 GB
image A binary string of a variable width Up to 2 GB

SQL Server Number Data Types

Type Description Storage
bit Allows to use 0.1 and NULL.
tinyint Allows to use numbers in the range of 0 to 255 1 byte
smallint Allows to use numbers in the range of -32,768 to 32,767 2 bytes
int Allows to use numbers in the range of -2,147,483,648 to 2,147,483,647 4 bytes
bigint Allows to use numbers in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8 bytes
decimal(p,s) Defines scale and precision numbers, allows to use numbers in the range of -10^38 to 10^38 –1 5–17 bytes
numeric(p,s) Defines scale and precision numbers, allows to use numbers in the range of -10^38 +1 to 10^38 –1.
The total amount of containable digits is defined in the p parameter which needs to have a value in the range of 1 to 38.
To define the amount of digits to be contained in the right side, you define the s parameter. It has to have a value in the range from 0 to p.
5-17 bytes
smallmoney Fiscal data in the range of -214,748,3648 to 214,748,3647 4 bytes
money Fiscal data in the range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
float(n) Data in the range of -1.79E + 308 to 1.79E + 308 of floating precision numbers.
Define the data record field byte length (4 or 8) in the n parameter.
4 or 8 bytes
real Data in the range of -3.40E + 38 to 3.40E + 38 of floating precision numbers. 4 bytes

SQL Server Date Data Types

Type Description Storage
datetime Has the range of January 1, 1753 to December 31, 9999 and the precision of 3.33 ms 8 bytes
datetime2 Has the range of January 1, 0001 to December 31, 9999 and the precision of 100 ns 6-8 bytes
smalldatetime Has the range of January 1, 1900 to June 6, 2079 and the precision of 1 minute 4 bytes
date Has the range of January 1, 0001 to December 31, 9999. Only good for containing a date 3 bytes
time Has the range of January 1, 0001 to December 31, 9999 and the precision of 100 ns. Only good for containing a date 3-5 bytes
datetimeoffset A duplicate of datetime2 with an offset of a time zone. 8-10 bytes
timestamp Contains a number that is unique and refreshes with every new row. It works around an internal clock (not real time). Use limited to a single timestamp per table

Other SQL Server Data Types

Type Description
sql_variant Contains a maximum of 8,000 bytes of different data types apart from timestamp, ntext and text
uniqueidentifier Contains a GUID
xml Contains up to 2GB of data formatted as XML
cursor Contains references to the database operation cursor
table Contains a set of results used for later processing

MySQL Data Types

MySQL Text Data Types

Type Description Storage
CHAR(size) Contains a string with a defined length (numbers, special characters, letters), defined using parentheses Up to 255 characters
VARCHAR(size) Contains a string with a defined length (numbers, special characters, letters), defined using parentheses Up to 255 characters (if more, will be changed to the text type)
TINYTEXT Contains a string Up to 255 characters
TEXT Contains a string Up to 65,535 characters
BLOB Contains data Up to 65,535 bytes
MEDIUMTEXT Contains a string Up to 16,777,215 characters
MEDIUMBLOB Contains data Up to 16,777,215 bytes
LONGTEXT Contains a string Up to 4,294,967,295 characters
LONGBLOB Contains data Up to 4,294,967,295 bytes
ENUM(x,y,z,etc.) Allows you to insert an available value list (if no value is defined, a blank one is created) Up to 65,535 bytes
SET A list with various choices of storage possibilities Up to 64 items

MySQL Number Data Types

Type Description
TINYINT(size) Can contain numbers from -128 to 127 or 0 to 255 UNSIGNED. The length is defined using parentheses.
SMALLINT(size) Can contain numbers from -32768 to 32767 or 0 to 65535 UNSIGNED. The length is defined using parentheses.
MEDIUMINT(size) Can contain numbers from -8388608 to 8388607 or 0 to 16777215 UNSIGNED. The length is defined using parentheses.
INT(size) Can contain numbers from -2147483648 to 2147483647 or 0 to 4294967295 UNSIGNED. The length is defined using parentheses.
BIGINT(size) Can contain numbers from -9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615 UNSIGNED. The length is defined using parentheses.
FLOAT(size,d) Contains a number with a floating decimal point. The length is defined using the parameter size d.
DOUBLE(size,d) Contains a big number with a floating decimal point. The length is defined using the parameter size d.
DECIMAL(size,d) Contains a big number with a floating decimal point. Used for fixed decimal points. The length is defined using the parameter size d. The above text type is contained as string.

Other MySQL Data Types

Type Description Format Length range
DATE() A date YYYY-MM-DD '1000-01-01' to '9999-12-31'
DATETIME() Date/Time YYYY-MM-DD HH:MI:SS '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP() A timestamp wuth its values contained as seconds from the start of Unix ('1970-01-01 00:00:00' UTC). YYYY-MM-DD HH:MI:SS '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME() A time HH:MI:SS '-838:59:59' to '838:59:59'
YEAR() Two or four digit format year. Range in four digit: 1901 to 2155. In two digit: 70 to 69 (1970 to 2069)

Microsoft Access Data Types

Type Description Storage
Text Needed to combine number with text Up to 255 characters
Memo Needed to work with a text of a large volume. Can be searched through but not sorted Up to 65,536 characters
Byte Allows to use a number between 0 and 255 1 byte
Integer Allows to use a number from -32,768 to 32,767 2 bytes
Long Allows to use a number from -2,147,483,648 to 2,147,483,647 4 bytes
Single Works with most decimals. Singular floating-point precision 4 bytes
Double Works with most decimals. Dual floating-point precision 8 bytes
Currency Needed to choose the country's currency. A 15 digit container with 4 decimal locations. 8 bytes
AutoNumber Assigns a unique number to each data record 4 bytes
Date/Time Needed when working with time and date 8 bytes
Yes/No Used for logical data record fields. True or False statements are used. No NULL values are allowed 1 bit
Ole Object Used to contain audio, pictures, BLOBs and video up to 1GB
Hyperlink Used to link web pages and files
Lookup Wizard Used to look up a drop-down option list 4 bytes
Learn SQL
Introduction
Syntax
Data Types
Server Data Types
Commands
Commands List
Wildcards
Constraints
Aggregate Functions
Date Functions
Date Format
Injection
SQL Server Hosting
Views
Auto-incrementation
SQL Operators
AS
AND & OR
IN
BETWEEN
WHERE
GROUP BY
HAVING
ORDER BY
LIKE
NOT
NOT EQUAL
UNION
NULL
NOT NULL
DEFAULT
UNIQUE
FOREIGN KEY
PRIMARY KEY
CHECK
Indexes
ALTER TABLE
CREATE DATABASE
CREATE TABLE
DELETE
DROP
INSERT INTO SELECT
INSERT INTO
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
UPDATE
FULL OUTER JOIN
INNER JOIN
JOIN
LEFT JOIN
RIGHT JOIN
AVG()
COUNT
FIRST
LAST
MAX
MIN()
SUM()
LEN
UCASE
MID
NOW
ROUND
FORMAT
LOWER
CONVERT
ISNULL