SQL Data Types

What do you mean by SQL Data Types?

Any object's data type is specified by the SQL Data Type attribute.These data types can be used while generating tables. Based on your needs, you can select a data type for a table column.
The nature of the data that can be recorded in a database table is represented by data types. For example, if we wish to store a string type of data in a specific column of a table, we must specify a string data type for that column.
For each database, data types are divided into three categories.

🚀Types of String Data
🚀Types of Numeric Data
🚀Time and Date Types of data

Data Types in MySQL, SQL Server and Oracle Databases

Microsoft Access Data Types

Text : Use for text or text and number combinations.

Memo :For greater volumes of text, utilise Memo. Up to 65,536 characters can be stored. A memo field cannot be sorted. They are, however, searchable.

Byte :Allows full numbers from 0 to 255 to be entered.
Storage : 1 Byte.

Integer :Allows for whole numbers in the range of -32,768 to 32,767.
Storage : 2 Byte.

Long : Allows numbers between -2,147,483,648 and 2,147,483,647 as whole numbers.
Storage : 4 Byte.

Single : Floating-point with a single precision. Will work with most decimals.
Storage : 4 Byte.

Double :Floating-point with double accuracy. Will work with most decimals
Storage : 8 Byte.

Currency : Use as a form of currency. Up to 15 digits of whole dollars, plus four decimal places, can be stored. You have the option of using the currency of any country.
Storage : 8 Byte.

AutoNumber : AutoNumber fields assign a unique number to each entry, usually starting at 1.
Storage : 4 Byte.

Data/Time : Dates and times are entered here.
Storage : 8 Byte.

Yes/No : Yes/No, True/False, or On/Off are all examples of logical fields. Use the True and False constants in your code (equivalent to -1 and 0). In Yes/No fields, null values are not permitted.
Storage : 1 Byte.

Ole Object : Can be used to store images, audio, video, and other BLOBs (Binary Large OBjects)

Hyperlinks : Links to additional files, such as web sites, are included.
Storage : upto 4GB.

Lookup Wizard : Allow you to type a list of choices that may then be selected from a drop-down menu.
Storage : 4 Byte.

MySQL Data Types - Text Types

CHAR(size) :Holds a string of a specific length (can contain letters, numbers, and special characters). In parenthesis, the fixed size is specified. Up to 255 characters can be stored.

VARCHAR(size) :Holds a string of varying length (can contain letters, numbers, and special characters). In parenthesis, the maximum size is indicated.Note: If you enter a value bigger than 255, it will be converted to TEXT.

TINYTEXT :Up to 255 characters can be stored.

TEXT : Data can be kept for up to 65,535 bytes.

BLOB :In the case of BLOBs (Binary Large OBjects). Data of up to 4,294,967,295 bytes can be saved.

MEDIUMTEXT : It can store a string of up to 16,777,215 characters.

MEDIUMBLOB :It can store a string of up to 4,294,967,295 characters.

LONGBLOB :In the case of BLOBs (Binary Large OBjects). Up to 4,294,967,295 bytes of data can be stored.

ENUM(x,y,z,etc.): It is a function that allows you to enter a list of possible values. An ENUM list can contain up to 65535 values.
Note that the values are sorted in the order in which they are entered.
ENUM('X','Y','Z') ENUM('X','Y','Z') ENUM('X','Y','Z') ENUM('X','Y'

SET : SET is similar to ENUM, except that it can hold up to 64 list items and can store multiple choices.

MySQL Data Types - Number Types

TINYINT(size) Holds a string of a specific length (can contain letters, numbers, and special characters). In parenthesis, the fixed size is specified. Up to 255 characters can be stored.

SMALLINT(size) Holds a string of varying length (can contain letters, numbers, and special characters). In parenthesis, the maximum size is indicated. Up to 255 characters can be stored. Note: If you enter a value bigger than 255, it will be converted to TEXT.Normal range is -128 to 127. UNSIGNED* from 0 to 255. In parenthesis, you can choose the maximum amount of digits.

MEDIUMINT(size) 0 to 65535 UNSIGNED*. 32768 to 32767 normal. In parenthesis, you can choose the maximum amount of digits.

INT(size) Normal range: -8388608 to 8388607. UNSIGNED* from 0 to 16777215 -2147483648 to 2147483647 typical is the maximum number of digits that can be given in parentheses. UNSIGNED* from 0 to 4294967295 In parenthesis, you can choose the maximum amount of digits.

BIGINT(size) 0 to 18446744073709551615 UNSIGNED*. -9223372036854775808 to 9223372036854775807 typical. In parenthesis, you can choose the maximum amount of digits.

FLOAT(size, d) A tiny number with a decimal point that is floating. The size option can be used to specify the maximum number of digits. The d option specifies the maximum number of digits to the right of the decimal point.

DOUBLE(size, d) A huge number with a decimal point that is floating. The size option can be used to specify the maximum number of digits. The d option specifies the maximum number of digits to the right of the decimal point.

DECIMAL(size, d) A DOUBLE that is stored as a string with a fixed decimal point. The size option can be used to specify the maximum number of digits. The d option specifies the maximum number of digits to the right of the decimal point.

UNSIGNED is an additional option for integer types. In most cases, the integer will change from a negative to a positive number. By using the UNSIGNED attribute, you can have that range start at zero rather than a negative integer.

MySQL Data Types - Date/Time Types

DATE() : YYYY-MM-DD is the standard format. Note that the range supported is '1000-01-01' to '9999-12-31'.

DATETIME(): The combination of a date and time. HH:MI:SS YYYY-MM-DD HH:MI:SS YYYY-MM-DD YYYY-MM-DD Y

TIMESTAMP(): A date and time mark. The number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC) is represented in TIMESTAMP values. HH:MI:SS YYYY-MM-DD HH:MI:SS YYYY-MM-DD YYYY-MM-DD Y The acceptable time range is from 1970-01-01 00:00:01 UTC to 2038-01-09 03:14:07 UTC.

TIME():A period of time has passed. HH:MI:SS HH:MI:SS HH:MI:SS HH:MI: Note that the range supported is '-838:59:59' to '838:59:59'.

YEAR():A year expressed as a two-digit or four-digit number. Note: Only four-digit values are permitted: 1901 to 2155. Values in two-digit format are allowed: 70 to 69, which correspond to the years 1970 to 2069.

SQL Server Data Types - String Types

char(n) :Character string with a fixed width. There is a limit of 8,000 characters.

varchar(n) :Character string with variable width. There is a limit of 8,000 characters.

varchar(max) :Character string with variable width. There are a total of 1,073,741,824 characters in this field.

text :Character string with variable width. Text data is limited to 2GB.

nchar :Unicode string with a fixed width. 4,000 characters is the maximum allowed.

nvarchar : Unicode string with variable width. 4,000 characters is the maximum allowed.

nvarchar(max) :Unicode string with variable width. There are a total of 536,870,912 characters in this field.

ntext :Unicode string with variable width. Maximum text data size is 2GB.

bit :Allows for a value of 0 or 1 or NULL.

binary(n) :Binary string with a fixed length. 8,000 bytes is the maximum size.

varbinary() :Binary string with variable width. 8,000 bytes is the maximum size.

varbinary(max) : Binary string with variable width. 2 GB maximum

SQL Server Data Types - Number Types

tinyint : Allows full numbers from 0 to 255 to be entered.

smallint : Allows for whole numbers in the range of -32,768 to 32,767.

int :Allows numbers between -2,147,483,648 and 2,147,483,647 as whole numbers.

bigint :Allows numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 to be used as whole numbers.

decimal(p, s) : Precision and scale numbers are fixed.
Allows for numbers between -1038 +1 and 1038.
The p option specifies the maximum number of digits that can be saved in total (both to the left and to the right of the decimal point). p must be a number between 1 and 38. The default value is 18.
The s option specifies the maximum number of digits to the right of the decimal point that can be saved. s must be a number between 0 and p. 0 is the default value.

numeric(p, s) : Precision and scale numbers are fixed.
Allows for numbers between -1038 +1 and 1038.
The p option specifies the maximum number of digits that can be saved in total (both to the left and to the right of the decimal point). p must be a number between 1 and 38. The default value is 18.
The s option specifies the maximum number of digits to the right of the decimal point that can be saved. s must be a number between 0 and p. 0 is the default value.

smallmoney :-214,748.3648 to 214,748.3647 monetary data.

money :-922,337,203,685,477.5808 to 922,337,203,685,477.5807 monetary data

float(n) :From -1.79E + 308 to 1.79E + 308, floating precision numerical data.
Whether the field should carry 4 or 8 bytes is determined by the n option. A 4-byte field is stored in float(24), and an 8-byte field is stored in float(53). n is set to 53 by default.

real : Data ranging from -3.40E + 38 to 3.40E + 38 in floating precision.

SQL Server Data Types - Date Types

datetime :With a precision of 3.33 milliseconds, from January 1, 1753 to December 31, 9999

datetime2 :With a precision of 100 nanoseconds from January 1, 0001 to December 31, 9999

smalldatetime :From January 1, 1900, to June 6, 2079, with a 1 minute accuracy

date :Only keep a date. From the 1st of January until the 31st of December, 9999

time :Only store a time with a precision of 100 nanoseconds.

datetimeoffset : Datetime2 with a time zone offset is the same as datetime2.

timestamp :Every time a row is generated or edited, this variable is updated with a new number. The timestamp value is calculated using an internal clock and does not reflect actual time. Only one timestamp variable is allowed per table.

Other Data Types

sql_variant :Except for text, ntext, and timestamp, it can store up to 8,000 bytes of data of various types.

uniqueidentifier :It keeps track of a globally uniquea identifier (GUID)

XML :Stores data in XML format. 2 GB maximum

cursor :This variable holds a reference to a cursor that is utilised in database operations.

table :Saves a set of results for later processing.

With this article at Logicmojo, you must have the complete idea of SQL Data Types.