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.

Stay updated with latest technology trends
Join DataFlair on Telegram!!

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.

 

Did you know we work 24x7 to provide you best tutorials
Please encourage us - write a review on Google | Facebook

1 Response

  1. SANKET KADU says:

    Which type of datatypes is require for data analysis

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.