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.
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. |
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. |
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. |
| 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 |
| 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 |