SQL Data Types ?:

aimtocode

SQL Data Type Definition:

In this tutorial, you will learn about most commonly used SQL data types including character string data types, numeric data types, and date time data types etc.

A data type specifies the type of data that column can hold such as character strings, numeric values, and date time values.

SQL supplies a set of basic data types that you can use for defining columns of tables. In this tutorial, we will cover the most commonly used SQL data types.

SQL most popularly used data types

  • STRING DATA TYPE
  • NUMERIC DATA TYPE
  • DATE and TIME DATA TYPE
  • MISCELLANEOUS DATA TYPES

MySQL String Data Types

String data types are normally used to store names, addresses, descriptions or any value which is containing letters and numbers including binary data, like image or audio files.

Data type Description
char(n) Stores fixed-length character string. Maximum length 8,000 characters.
varchar(n) Stores variable-length character string. Maximum length 8,000 characters.
varchar(max) Stores variable-length character string. Here, max indicates that the maximum storage size is 2 GB.
text Stores variable-length character string. Maximum storage size is 2 GB.
nchar Stores fixed-length Unicode string. Maximum length 4,000 characters.
nvarchar Stores variable-length Unicode string. Maximum length 4,000 characters.
nvarchar(max) Stores variable-length Unicode string. Here, max indicates that the maximum storage size is 2 GB.
ntext Stores variable-length Unicode string. Maximum storage size is 2 GB.
binary(n) Stores fixed-length binary data. Maximum storage size is 8,000 bytes.
varbinary(n) Stores variable-length binary data. Maximum storage size is 8,000 bytes.
varbinary(max) Stores variable-length binary data. Here, max indicates that the maximum storage size is 2 GB.
image Stores variable-length binary data. Maximum storage size is 8,000 bytes.

SQL Server Data Types

MySQL Numeric Data Type

If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.

Data type Description
bit Allows you to store a value 1, 0, or NULL.
tinyint Stores integer values in the range from 0 to 255.
smallint Stores integer values in the range from -32,768 to 32,767.
int Stores integer values in the range from -2,147,483,648 to 2,147,483,647.
bigint Stores integer values in the range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
decimal(p,s) Stores fixed precision and scale numbers. Valid values are from 10^38 +1 through 10^38 - 1. See below for more details.
numeric(p,s) The numeric datatype is functionally equivalent to decimal.
smallmoney Allows you to store monetary or currency values accurately in the range from -214,748.3648 to 214,748.3647.
money Allows you to store monetary or currency values accurately in the range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
float(n) Stores floating point numeric values. Valid values are from -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308.
real Stores floating point numeric values. Valid values are from -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.

MySQL Date and Time Data Types

Date and Time data types are normally used to store data like date of birth, hiring date, date and time when a record is created or updated inside table, etc.

Data type Description
date Stores a date value in the range from 0001-01-01 (January 1, 1) to
9999-12-31 (December 31, 9999).
time Stores time of a day with the accuracy of 100 nanoseconds. Valid values are from 00:00:00.0000000 to 23:59:59.9999999.
datetime Stores a combined date and time value with an accuracy of 3.33 milliseconds. Valid date range for datetime is from 1753-01-01 (January 1, 1753) to 9999-12-31 (December 31, 9999).
datetime2 The datetime2 is an extension of the datetime data type that has a larger date range. Valid date range for datetime2 is from 0001-01-01 (January 1, 1) to 9999-12-31 (December 31, 9999).
smalldatetime Stores a combined date and time value with an accuracy of 1 minute. Valid date range for smalldatetime is from 1900-01-01 (January 1, 1900) to
2079-06-06 (June 6, 2079).
datetimeoffset Same as datetime2 with the addition of a time zone offset. Default format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. Valid range for time zone offset is from -14:00 to +14:00.
timestamp In SQL Server timestamp is the synonym for the rowversion data type which automatically generate, unique binary numbers within a database. The rowversion is generally used for version-stamping table rows.

Other Data Types:

Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database operations
table Stores a result-set for later processing

Microsoft Access Data Types

Data type Description Storage
Text Use for text or combinations of text and numbers. 255 characters maximum  
Memo Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable  
Byte Allows whole numbers from 0 to 255 1 byte
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
Single Single precision floating-point. Will handle most decimals 4 bytes
Double Double precision floating-point. Will handle most decimals 8 bytes
Currency Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country's currency to use 8 bytes
AutoNumber AutoNumber fields automatically give each record its own number, usually starting at 1 4 bytes
Date/Time Use for dates and times 8 bytes
Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields 1 bit
Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large OBjects) up to 1GB
Hyperlink Contain links to other files, including web pages  
Lookup Wizard Let you type a list of options, which can then be chosen from a drop-down list 4 bytes
aimtocode