SQL Data Types | Data Types in SQL Server

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.