SQL Data Types | Data Types in SQL Server

FREE Online Courses: Knowledge Awaits – Click for Free Access!

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.NoDataTypeDescription
1binaryStores fixed-length binary data with a maximum capacity of 8000 bytes.
2varbinaryStores variable-length binary data with a maximum capacity of 8000 bytes.
3varbinary(max)Stores variable-length binary data with a maximum capacity of 231 bytes.
4imageStores 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.NoDatatypeDescriptionRange
1bigintHolds the big integer values.[-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808]

Both inclusive

2intHolds the integer values.[-2,147,483,648 to 2,147,483,648] Both inclusive
3smallintHolds small integer values.[-32,768 to 32,768] Both inclusive
4tinyintHolds a tiny range of int from 0 to 255.[0 to 255] Both inclusive
5bitHolds the value 0 or 1.0 or 1
6decimalHolds the decimal values.[-10^38+1 to 10^38-1] Both inclusive
7numericHolds the numerical values[-10^38+1 to 10^38-1] Both inclusive
8moneyHolds the monetary values.[-922,337,203,685,477.5808 to 922,337,203,685,477.5808]

Both inclusive

9smallmoneyHolds 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.NoDatatypeDescriptionRange
1floatHolds the float values.-1.79E+308  to 1.79E+308
2realHolds 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.NoDataTypeDescription
1charHolds fixed length characters with a maximum capacity of 8000 characters.
2varcharHolds variable-length non-Unicode characters with a max capacity of 8000 characters.
3varchar(max)Holds Variable-length non-Unicode characters with a max capacity of 231 characters.
4textHolds 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.NoDataTypeDescription
1ncharStores Unicode fixed length characters with a max capacity of 4000 characters.
2NvarcharStores Unicode variable-length characters with a max capacity of 4000 characters.
3nvarchar(max)Stores Unicode variable-length characters with a max capacity of 231 characters.
4ntextStores 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.NoDatatypeDescriptionRange
1datetimeHolds the value of date and time both in a single variable.Jan 1, 1753 to Dec 31, 9999
2smalldatetimeHolds the smaller range of  values of date and time both in a single variable.Jan 1, 1753 to Jun 6, 2079
3dateHolds the value of the date.No Limits
4timeHolds the value of time.No Limits
5yearHolds the two-digit or four-digit value for the variables of type year.No Limits
6timestampHolds the value of time in the variable.No Limit

7. Miscellaneous Data Types

Further classifications of miscellaneous data types are as follows:

Sr.NoDatatypeDescription
1clobHolds large character objects with a max capacity of 2Gb.
2blobHolds large binary objects.
3XMLHolds the incoming XML data.
4JSONHolds the incoming JSON data.
5cursorHolds the cursor object.
6UUIDHolds the universally unique identifiers.
7bfileHolds 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 like this article? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

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 *