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

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, thereforeSQL 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.

The following are covered in this article:

• • Exact Numeric Data Types
  • Approximate Numeric Data Types

• Character String Data Types

  • Unicode Character Strings Data Types
• Date & Time Data Types

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
  • Approximate Numeric Data Types

Exact Numeric Data Types

Data Type Description / Range Storage Description FROM TO bit An integer which can either be 0, 1, or NULL. – tinyint Allows whole numbers 0 255 1 byte smallint Allows whole numbers -32,768 32,767 2 bytes int Allows whole numbers -2,147,483,648 2,147,483,647 4 bytes bigint Allows whole numbers -9,223,372,036,854,775,808 9,223,372,036,854,775,807 8 bytes numeric(p,s) Allows a numeric value. Where ‘ p ‘ is precision value and ‘ s ‘ is scale value -10^38 +1 10^38 -1 5-17 bytes decimal(p,s) Allows a decimal value. Where ‘ p ‘ is precision value and ‘ s ‘ is scale value -10^38 +1 10^38 -1 5-17 bytes smallmoney Allows data as currency -214,748.3648 +214,748.3647 4 bytes money Allows data as currency -922,337,203,685,477.5808 922,337,203,685,477.5807 8 bytes

Now, let us look into Approximate Numeric Data Types.

Approximate Numeric Data Types

Data Type Description / Range Storage Description FROM TO float(n) Allows Floating precision number data -1.79E + 308 1.79E + 308 4 or 8 bytes real Allows Floating precision number data -3.40E + 38 3.40E + 38 4 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 Type Description / Maximum Size Storage Description Maximum Size text Allows a v ariable length character string 2GB of text data 4 bytes + number of chars varchar(max) Allows a v ariable length character string 2E + 31 characters 2 bytes + number of chars varchar Allows a v ariable length character string 8,000 characters 2 bytes + number of chars char Allows a fixed length character string 8,000 characters Defined 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 Type Description / Maximum Size Storage Description Maximum Size ntext Allows a variable length Unicode string 2GB of text data 4 bytes + number of chars nvarchar(max) Allows a variable length Unicode string 2E + 31 characters 2 bytes + number of chars nvarchar Allows a variable length Unicode string 4,000 characters 2 bytes + number of chars nchar Allows a f ixed length Unicode string 4,000 characters Defined 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 Type Description / Maximum Size Description Maximum Size image Allows a variable length binary data 2,147,483,647 bytes varbinary(max) Allows a variable length binary data 2E + 31 bytes varbinary Allows a variable length binary data 8,000 bytes binary Allows a f ixed length binary data 8,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 Type Description / Range Storage Description FROM TO date Stores date in the format of Year, Month & Days. January 1, 0001 December 31, 9999 3 bytes time Stores time in the format of Hours, Minutes & Seconds. 3-5 bytes datetime Stores both date and time(with an accuracy of 3.33 milliseconds) January 1, 1753 December 31, 9999 8 bytes datetime2 Stores both date and time(with an accuracy of 100 nanoseconds) January 1, 0001 December 31, 9999 6-8 bytes smalldatetime Stores both date and time(with an accuracy of 1 minute) January 1, 1900 June 6, 2079  4 bytes datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes timestamp Stores 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 Type Description table This data type stores a result set for later processing. xml This data type stores XML formatted data. cursor This data type provides a reference to a cursor object. uniqueidentifier This data type stores a globally unique identifier (GUID). sql_variant This data type s tores 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.

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