SQL Data Types | Data Types in SQL Server
Job-ready Online Courses: Click for Success - Start Now!
In this tutorial, we will learn about the data types available in SQL. Let us dive deep into the data types.
What are the Data types and their Needs?
We have a huge volume of data available to us, and it would be complicated to handle this data if we store all the data in a single data type.
Data type comes in handy when we need to handle a variety of data available to us.
For example: If we consider the data of a student, we will see that we have a diversity of data types being used.
The name of the candidate is a string or varchar, the roll number is an int value, date of birth is a date object. With this example, we can clearly understand the need for data types in the creation of a database.
Available Datatypes
Some of the data types available in SQL are:
1. Binary Datatype
Binary datatype has further divisions as follows:
| Sr.No | DataType | Description |
| 1 | binary | Stores fixed-length binary data with a maximum capacity of 8000 bytes. |
| 2 | varbinary | Stores variable-length binary data with a maximum capacity of 8000 bytes. |
| 3 | varbinary(max) | Stores variable-length binary data with a maximum capacity of 231 bytes. |
| 4 | image | Stores variable-length binary data with a maximum capacity of 2,147,483,647 bytes |
2. Exact Numeric Datatype
Further divisions of an exact numeric datatype are as follows:
| Sr.No | Datatype | Description | Range |
| 1 | bigint | Holds the big integer values. | [-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808] Both inclusive |
| 2 | int | Holds the integer values. | [-2,147,483,648 to 2,147,483,648] Both inclusive |
| 3 | smallint | Holds small integer values. | [-32,768 to 32,768] Both inclusive |
| 4 | tinyint | Holds a tiny range of int from 0 to 255. | [0 to 255] Both inclusive |
| 5 | bit | Holds the value 0 or 1. | 0 or 1 |
| 6 | decimal | Holds the decimal values. | [-10^38+1 to 10^38-1] Both inclusive |
| 7 | numeric | Holds the numerical values | [-10^38+1 to 10^38-1] Both inclusive |
| 8 | money | Holds the monetary values. | [-922,337,203,685,477.5808 to 922,337,203,685,477.5808] Both inclusive |
| 9 | smallmoney | Holds small monetary values. | [-214,748.3648 to 214,748.3648] Both inclusive |
3. Approximate Numeric Datatype
The divisions of an approximate numeric datatype are as follows:
| Sr.No | Datatype | Description | Range |
| 1 | float | Holds the float values. | -1.79E+308Â to 1.79E+308 |
| 2 | real | Holds the real numerical values. | -3.40E+38 to 3.40E+38 |
4. Character String Datatype
Further classifications of character and string data types are as follows:
| Sr.No | DataType | Description |
| 1 | char | Holds fixed length characters with a maximum capacity of 8000 characters. |
| 2 | varchar | Holds variable-length non-Unicode characters with a max capacity of 8000 characters. |
| 3 | varchar(max) | Holds Variable-length non-Unicode characters with a max capacity of 231 characters. |
| 4 | text | Holds Variable-length non-Unicode data with a max capacity of 2,147,483,647 characters. |
5. Unicode character String Datatype
Further divisions of Unicode character datatype are as follows:
| Sr.No | DataType | Description |
| 1 | nchar | Stores Unicode fixed length characters with a max capacity of 4000 characters. |
| 2 | Nvarchar | Stores Unicode variable-length characters with a max capacity of 4000 characters. |
| 3 | nvarchar(max) | Stores Unicode variable-length characters with a max capacity of 231 characters. |
| 4 | ntext | Stores Unicode variable-length characters with a max capacity of 1 GB characters. |
6. Date and time Datatype
Further classifications of date and time datatype are as follows:
| Sr.No | Datatype | Description | Range |
| 1 | datetime | Holds the value of date and time both in a single variable. | Jan 1, 1753 to Dec 31, 9999 |
| 2 | smalldatetime | Holds the smaller range of values of date and time both in a single variable. | Jan 1, 1753 to Jun 6, 2079 |
| 3 | date | Holds the value of the date. | No Limits |
| 4 | time | Holds the value of time. | No Limits |
| 5 | year | Holds the two-digit or four-digit value for the variables of type year. | No Limits |
| 6 | timestamp | Holds the value of time in the variable. | No Limit |
7. Miscellaneous Data Types
Further classifications of miscellaneous data types are as follows:
| Sr.No | Datatype | Description |
| 1 | clob | Holds large character objects with a max capacity of 2Gb. |
| 2 | blob | Holds large binary objects. |
| 3 | XML | Holds the incoming XML data. |
| 4 | JSON | Holds the incoming JSON data. |
| 5 | cursor | Holds the cursor object. |
| 6 | UUID | Holds the universally unique identifiers. |
| 7 | bfile | Holds the binary data stored in external files. |
Summary
In this tutorial, we saw the need and use of data types in SQL. Some of the widely used data types in SQL are int, varchar, date object, boolean, etc.
When we talk about data types, we need to know about the data that is to be put into the column. Once we study and understand the data to be put in the column, we can decide the data type of the column.
When we assign the column with the data type, the insertion queries work only if the data of the required data type is being inserted else the command fails and exits. We will continue learning more in the next tutorials.
Your 15 seconds will encourage us to work even harder
Please share your happy experience on Google
Which type of datatypes is require for data analysis