SQL Data Types
A column’s data type is essentially the type of data format that will be used to store the data in each cell; examples include any type of integer, character, money, date and time, binary, etc. We’ll acquire in-depth information about SQL Data Types in this tutorial.
SQL Data Types
An SQL developer must know what data type will be stored inside each column while creating a table. The data type guideline for SQL is to understand what type of data is expected inside each column and it also identifies how SQL will interact with the stored data.
For every database, data types are primarily classified into three categories.
-
Numeric Datatypes
-
Date and Time Datatypes
-
String Datatypes
Like in other programming languages, SQL also has certain datatypes available. A brief idea of all the datatypes is discussed below.
Numeric Data Types in MYSQL
Exact Numeric Datatype
There are nine subtypes which are given below in the table. The table contains the range of data in a particular type.
| Data Type | From | To |
|---|---|---|
| BigInt | -263 (-9,223,372,036,854,775,808) | 263-1 (9,223,372,036,854,775,807) |
| Int | -231 (-2,147,483,648) | 231-1 (2,147,483,647) |
| SmallInt | -215 (-32,768) | 215-1 (32,767) |
| TinyInt | 0 | 28-1 (255) |
| Bit | 0 | 1 |
| Decimal | -1038+1 | 1038-1 |
| Numeric | -1038+1 | 1038-1 |
| Money | -922,337,203,685,477.5808 | 922,337,203,685,477.5807 |
| SmallMoney | -214,748.3648 | 214,748.3647 |
Approximate Numeric Datatype
The subtypes of this datatype are given in the table with the range.
| Data Type | From | To |
|---|---|---|
| Float | -1.79E+308 | 1.79E+308 |
| Real | -3.40E+38 | 3.40E+38 |
String Data Types in MYSQL
Character String Datatype
The subtypes are given in below table
| Data Type | Description |
|---|---|
| char | The maximum length of 8000 characters (Fixed-Length non-Unicode Characters) |
| varchar | The maximum length of 8000 characters (Variable-Length non-Unicode Characters) |
| varchar(max) | The maximum length of 2,147,483,647 characters (SQL Server 2005 only; Variable Length non-Unicode data) |
| text | The maximum length of 2,147,483,647 characters (Variable Length non-Unicode data) |
Unicode Character String Datatype
The details are given in below table
| Data Type | Description |
|---|---|
| nchar | The maximum length of 4000 characters (Fixed-Length Unicode Characters) |
| nvarchar | The maximum length of 4000 characters (Variable-Length Unicode Characters) |
| nvarchar(max) | The maximum length of 2,147,483,647 characters (SQL Server 2005 only; Variable Length Unicode data) |
Server String Data Type in SQL
There are four subtypes of this datatype which are given below:
| Data Type | Description |
|---|---|
| binary | The maximum length of 8000 bytes (Fixed-Length binary data) |
| varbinary | The maximum length of 8000 bytes (Variable Length binary data) |
| varbinary(max) | The maximum length of 2,147,483,647 bytes (SQL Server 2005 only; Variable Length binary data) |
| text | Maximum Length of 2,147,483,647 bytes (Variable Length binary data) |
Server Date and Time Data Type in SQL
The details are given in the below table.
| Data Type | Description |
|---|---|
| DATE | A data type used to store the date in a record. |
| TIME | A data type used to store the time in a record. |
| DATETIME | A data type used to store both the date and time in a record. |
Other Data Types
XML Datatype
XML data type allows storage of XML documents and fragments in a SQL Server database
| Data Type | Description |
|---|---|
| XML | A data type used to store data in the format of XML. |
Spatial Datatype
A datatype is used for storing planar spatial data, such as points, lines, and polygons, in a database table.
| Data Type | Description |
|---|---|
| Geometry | A data type used for storing planar spatial data, such as points, lines, and polygons, in a database table. |
Array Datatype
SQL Server does not have a built-in array datatype. However, it is possible to simulate arrays using tables or XML data types.
This text discusses the properties of data types in MYSQL.
SQL data types define the type of data that can be stored in a database column or variable. Here are the most common SQL data types:
| Data Type | Properties |
|---|---|
| Numeric Data Types | Used to store numeric values. Examples include INT, BIGINT, DECIMAL, and FLOAT. |
| Character Data Types | Used to store character strings. Examples include CHAR, VARCHAR, and TEXT. |
| Date and Time Data Types | Used to store date and time values. Examples include DATE, TIME, and TIMESTAMP. |
| Binary Data Types | Used to store binary data, such as images or audio files. Examples include BLOB and BYTEA. |
| Boolean Data Type | Used to store logical values. The only possible values are TRUE and FALSE. |
| Interval Data Types | Used to store intervals of time. Examples include INTERVAL YEAR, INTERVAL MONTH, and INTERVAL DAY. |
| Array Data Types | Used to store arrays of values. Examples include ARRAY and JSON. |
| XML Data Type | Used to store XML data. |
| Spatial Data Types | Used to store geometric or geographic data. Examples include POINT, LINE, and POLYGON. |
Different databases may have different variations of these data types, or they may have additional data types not listed here. Understanding SQL data types are important for creating tables and working with data in a database, as it affects how data is stored and processed.