SQL Data Types for MySQL, SQL Server

While Creating database table it is necessary that each column must have a name a data type. So that it is very much necessary for SQL developer must decide which data types is used for creating columns.

Basically, there are three types of data types in SQL.

  1. String
  2. Numeric
  3. Date

1)   String Data Types: –

A string is generally considered as a data type and is often implemented as an array data structure of bytes (or words) that stores a sequence of elements, typically characters, using some character encoding. String may also denote more general arrays or other sequence (or list) data types and structures.

Data typeDescriptionMax sizeStorage
char(n)Fixed width character string8,000 charactersDefined width
varchar(n)Variable width character string8,000 characters2 bytes + number of chars
varchar(max)Variable width character string1,073,741,824 characters2 bytes + number of chars
textVariable width character string2GB of text data4 bytes + number of chars
ncharFixed width Unicode string4,000 charactersDefined width x 2
nvarcharVariable width Unicode string4,000 characters 
nvarchar(max)Variable width Unicode string536,870,912 characters 
ntextVariable width Unicode string2GB of text data 
binary(n)Fixed width binary string8,000 bytes 
varbinaryVariable width binary string8,000 bytes 
varbinary(max)Variable width binary string2GB 
imageVariable width binary string2GB 

2)   Numeric Data Types: –

The numeric data type in SQL is one of the most widely used data types to store numeric values. Numbers in SQL can be either exact (NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate (DOUBLE PRECISION, FLOAT, and REAL). The exact numeric type, precision, and scale are preserved.

Data typeDescriptionStorage
bitInteger that can be 0, 1, or NULL 
tinyintAllows whole numbers from 0 to 2551 byte
smallintAllows whole numbers between -32,768 and 32,7672 bytes
intAllows whole numbers between -2,147,483,648 and 2,147,483,6474 bytes
bigintAllows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,8078 bytes
decimal (p, s)Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 05-17 bytes
numeric (p, s)Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 05-17 bytes
smallmoneyMonetary data from -214,748.3648 to 214,748.36474 bytes
moneyMonetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.58078 bytes
float(n)Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float (24) holds a 4-byte field and float (53) holds an 8-byte field. Default value of n is 53.4 or 8 bytes
realFloating precision number data from -3.40E + 38 to 3.40E + 384 bytes

3)   Date and Time Data Types: –

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in \’ YYYY-MM-DD hh:mm: ss \’ format. 

Data typeDescriptionStorage
datetimeFrom January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds8 bytes
datetime2From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds6-8 bytes
smalldatetimeFrom January 1, 1900 to June 6, 2079 with an accuracy of 1 minute4 bytes
dateStore a date only. From January 1, 0001 to December 31, 99993 bytes
timeStore a time only to an accuracy of 100 nanoseconds3-5 bytes
datetimeoffsetThe same as datetime2 with the addition of a time zone offset8-10 bytes
timestampStores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable