Understanding SQL Data Types – All You Need To Know About SQL Data Types

Last updated on Mar 18,2024 9.2K Views
Become a Certified Professional

Understanding SQL Data Types – All You Need To Know About SQL Data Types

edureka.co

With the amount of data present in the world, it is almost next to impossible, to manage data without proper databases. SQL is the core of relational databases which provides you with various options to deal with data, therefore SQL skills are indispensable in most of the job roles. In this article on SQL Data types, we are going to discuss the different data types used in SQL.

Table of Content

The following are covered in this article:

So, let us get started with each one of them.

SQL Data Types: Numeric Types

This section of the article will talk about the numeric data types. These data types allow both signed and unsigned integers. I have divided the numeric data types into the following two sections:

Exact Numeric Data Types

Data TypeDescription / Range
Storage
DescriptionFROMTO
bit An integer which can either be 0, 1, or NULL. –
tinyint Allows whole numbers  0255 1 byte
smallint Allows whole numbers-32,76832,7672 bytes
intAllows whole numbers-2,147,483,6482,147,483,6474 bytes
bigintAllows whole numbers-9,223,372,036,854,775,8089,223,372,036,854,775,8078 bytes
numeric(p,s) Allows a numeric value. Where ‘p‘ is precision value and ‘s‘ is scale value-10^38 +110^38 -15-17 bytes
decimal(p,s) Allows a decimal value. Where ‘p‘ is precision value and ‘s‘ is scale value-10^38 +110^38 -15-17 bytes
smallmoneyAllows data as currency-214,748.3648+214,748.36474 bytes
moneyAllows data as currency-922,337,203,685,477.5808922,337,203,685,477.58078 bytes

Now, let us look into Approximate Numeric Data Types.

Approximate Numeric Data Types

Data TypeDescription / Range
Storage
DescriptionFROMTO
float(n)Allows Floating precision number data-1.79E + 3081.79E + 3084 or 8 bytes
realAllows Floating precision number data-3.40E + 383.40E + 384 bytes

Next, in this article let us look into the character string data types used in SQL.

SQL Data Types: Character String Data Types

This section of the article will talk about the character data types. These data types allow characters of fixed and variable length. Refer to the below table.

Data TypeDescription / Maximum Size
Storage
DescriptionMaximum Size
textAllows a variable length character string2GB of text data4 bytes + number of chars
varchar(max)Allows a variable length character string2E + 31 characters2 bytes + number of chars
varcharAllows a variable length character string8,000 characters2 bytes + number of chars
charAllows a fixed length character string8,000 charactersDefined width

NOTE:

Ques: Why cannot we declare a value greater than VARCHAR(8000)? Ans: VARCHAR(n) is responsible for storing the data in a row page. Since in every page, the page header occupies the first 96 bytes and only 8096 bytes of data (8192 -96) are available for the row overhead and offsets, you cannot declare a value greater then VARCHAR(8000).

Also, I have extended the character strings data type sections to Unicode data types since our industry uses the Unicode for consistent representation and handling of text in most of the world’s writing systems.

Unicode Character Strings Data Types

Data TypeDescription / Maximum Size
Storage
DescriptionMaximum Size
ntextAllows a variable length Unicode string2GB of text data4 bytes + number of chars
nvarchar(max)Allows a variable length Unicode string2E + 31 characters2 bytes + number of chars
nvarcharAllows a variable length Unicode string4,000 characters2 bytes + number of chars
ncharAllows a fixed length Unicode string4,000 charactersDefined width * 2

Next, in this article let us look into the binary data types used in SQL.

SQL Data Types: Binary Data Types

This section of the article will talk about binary data types. These data types allow binary values of fixed and variable length. Refer to the below table.

Data TypeDescription / Maximum Size
DescriptionMaximum Size
imageAllows a variable length binary data2,147,483,647 bytes
varbinary(max)Allows a variable length binary data2E + 31 bytes
varbinaryAllows a variable length binary data8,000 bytes
binaryAllows a fixed length binary data8,000 bytes

Next, in this article let us look into the date and time data types used in SQL.

SQL Data Types: Date & Time Data Types

This section of the article will talk about the date and time data types. These data types allow different formats of date and time. Refer to the below table.

Data TypeDescription / Range
Storage
DescriptionFROMTO
date Stores date in the format of Year, Month & Days. January 1, 0001 December 31, 99993 bytes
timeStores time in the format of Hours, Minutes & Seconds.3-5 bytes
datetimeStores both date and time(with an accuracy of 3.33 milliseconds)January 1, 1753December 31, 99998 bytes
datetime2Stores both date and time(with an accuracy of 100 nanoseconds)January 1, 0001December 31, 99996-8 bytes
smalldatetimeStores both date and time(with an accuracy of 1 minute)January 1, 1900June 6, 2079 4 bytes
datetimeoffsetThe same as datetime2 with the addition of a time zone offset8-10 bytes
timestampStores a unique number which gets updated every time a row gets created or modified.

Next, in this article let us look into the miscellaneous data types available in SQL.

SQL Data Types: Other Data Types

This section of the article will talk about the data types that cannot be categorized into the above categories. Refer to the below table.

Data TypeDescription
tableThis data type stores a result set for later processing.
xmlThis data type stores XML formatted data.
cursorThis data type provides a reference to a cursor object.
uniqueidentifierThis data type stores a globally unique identifier (GUID).
sql_variant
This data type stores values of various SQL supported data types except text, ntext, and timestamp.

Now that you know the data types in SQL, I’m sure you’re curious to learn more SQL. Here’s a list of articles that you get started with:

  1. What is MySQL?
  2. MySQL Data Types
  3. MySQL Workbench
  4. Spark SQL
  5. SQL Interview Questions

So, folks, that’s an end to this article. I hope you enjoyed reading this article. We have seen the different data types that will help you write queries and play around with your databases. If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.

For details, You can even check out how to manage databases on SQL Server and its concepts with the Microsoft SQL Server Certification course.

Got a question for us? Please mention it in the comments section of this article and I will get back to you.

BROWSE COURSES
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.
REGISTER FOR FREE WEBINAR Analyzing Customer-Product Relationships for Business Growth with Tableau