Numeric Data Type SQL

SQL Numeric Data Type

Before learning about numeric data types in SQL, let us first briefly learn about SQL and Database Management Systems. SQL or Structured Query Language is used to manipulate and communicate with the data stored as tables in the database, though the underlying database management system can vary in supported types and behavior. You can build these fundamentals in a free SQL using MySQL online course that covers queries, data retrieval, and relational design. Before we start manipulating the data, we need to first insert the data into the tables as well. A SQL query (called query in short) is used to fetch the records from tables of a database.

MySQL is a commonly known DBMS, and SQL Server Data Types are often grouped similarly, with a wide range of data types such as char, varchar, binary, text, boolean, bit, integer, float, date, etc. Learners can explore these concepts in more depth through a free DBMS online course with certificate. We can use these data types to define the data of our rows and columns. We can broadly categorize these data types into four main categories: string data type, numeric, date and time, and special data types. For character data, CHAR is used for fixed length values, while VARCHAR is used for variable length or variable length text, which helps store text based on expected size and maximum length. Unicode options such as NCHAR for fixed length unicode characters and NVARCHAR for variable length unicode characters support multilingual content as a correct data type choice in systems like sql server. Choosing the correct data type is important for database design because it supports efficient storage and better query performance.

The numeric data type in SQL is one of the most widely used data types to store numeric values. Below is the list of data types that are included under the numeric data type in SQL:

• INTEGER or INT

• SMALL INT

• DECIMAL or DEC or FIXED

• NUMERIC

• FLOAT

• REAL

• DOUBLE

• PRECISION

Note: In MySQL, DOUBLE is the synonym for DOUBLE PRECISION. REAL is also a synonym for DOUBLE PRECISION.

Let us learn about the syntax, use cases, examples, and mistakes related to the numeric data type in SQL.

Numbers in SQL can be either exact numbers (like NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate numbers (like DOUBLE PRECISION, FLOAT, and REAL). In exact numeric type, precision and scale are preserved.

For example, we can fix the significant digits and precision for a numeric data type. On the other, in approximate numeric types, the precision needs to be preserved, but the scale is not (it can be floating). Refer to the floating and numeric data type section for better understanding.

Syntax:

Here, numeric is a data type that takes 5-17 bytes storage. Precision or p is an integer representing the total number of allowed digits in the specified column. Its default value is 18, and it can be avoided. Allows numbers from (−1038+1)(-10^{38} + 1) to (1038−1)(10^{38} - 1). The radix or base of the precision digit is either decimal (base-10) or binary (base-2). So, the number stored in the specified column can be base-2 or base-10.

Scale or s is also an integer value (s must be a value from 0 top, and its default value is 0) that is used to define the maximum number of decimal places allowed to the right or left of the decimal point. If the number is negative, the left decimal places are considered. Else, the right decimal places are considered.

Let us take the example of a company database to understand the syntax better. Suppose we want to create an employee table with three columns, namely account name, account number, and salary. The salary column is a numeric data type with a scale of 2 (two digits in the right place of the decimal) and a precision of 8 (i.e., the salary can be an eight-digit number).

Suppose we have entered data with the name Sushant, account number as 25, and salary as 51000. The database will look like this:

Refer to the image below to see the kind of numbers allowed in the salary column.

Generally, the numeric data types in SQL have an extra option of UNSIGNED and ZEROFILL. If we add the UNSIGNED option to a column, MySQL will disallow the negative values for that column. On the other hand, if we add the ZEROFILL option to a column, MySQL will automatically add the UNSIGNED attribute to the column.

Numeric-Type Mistakes and Common Pitfalls

There may arise some errors in the numeric column if we try to cross the precision limit. Suppose that the data inserted is too large as per the precision limit, an error is generated. The error may look like this: ERROR 1118 (42000): Row size too large.

Let us take the same employee table example and try inserting values larger than the specified precision limit.

Error is generated in output. Now let us insert data in the precise limit.

No error will be generated because we have inserted the data as per the limits specified during the table creation. Errors are usually generated when we do not consider the constraint conditions of a particular column, which are enforced through integrity constraints in DBMS. The employee table will look like this:

Note: Only two digits after the decimal point are kept in the database because the scale was set to 2.

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

The Integer Data Types

The integer data type is used to store whole numbers (numbers without the decimal point or simply non-decimal numbers). A small integer can be preferable when the value range is limited. The numbers can be negative. The integer data type consists of SMALLINT, INTEGER, TINYINT, and BIGINT, which are among the common data types and the most common data types for numeric values.

Syntax:

Let us take the example of a college database to understand the syntax better. Smaller fixed-width integer columns can improve query performance and indexing efficiency, because the CPU processes fixed-width types like INT faster than variable-width types like VARCHAR and indexes on smaller data types take up less memory. Suppose we want to create a student table with three columns: student id, student name, and dues. The dues column is an integer column, and later we can use DML commands in SQL such as INSERT, UPDATE, and DELETE to manage its values.

Integer-Type Mistakes

Integer data type rounds off the decimal number (for example, 123.5 will get rounded off to 124). This round-off feature of the integer data type may cause inconsistency at some point. If we want exact precision, we should use the decimal data type such as DECIMAL or NUMERIC, which are exact numerics suited for precise numeric values and fixed precision. By contrast, floating point numbers are used to store approximate values, so approximate values can introduce rounding errors and are risky for financial data, currency values, ratios, and other real world data; for example, define a salary DECIMAL or price DECIMAL column when accuracy matters.

Let us take the example of the same student table and try to insert decimal values in the dues column.

Student table

Float Data Types and Rounding Errors

The FLOAT and REAL data types are used to store approximate values. Concepts like exponents and precision are also common in programming languages when working with scientific notation for large or small numbers. The floating data type consists of two parts, namely significant value(M) and exponent value(D). The significant value is a signed numeric value that is the maximum number of digits, the precision.

The exponent value is also a positive value that is used to define the number of digits to the right of the decimal point. For example: if we insert 878.00009 into a FLOAT(7,4) column, the approximate result is 878.0001. These floating point numbers are best for measurements or scientific approximate values, while DECIMAL is better when exact results are required. Because they store approximate values, calculations can sometimes introduce rounding errors.

Converting Decimal and Numeric Data

A constant with a decimal point in SQL statements is automatically converted into a numeric data value. The conversion of decimal or numeric value to a floating-point number or float may lead to a loss of precision. On the other hand, the conversion of a floating-point number to a decimal or numeric value may lead to the loss of data (also known as an overflow of data). For precision-sensitive values, DECIMAL or NUMERIC stores precise numeric values with fixed precision, unlike approximate float conversion.

By default, the SQL rounds off a decimal or numeric value with lower precision and scale. We can change this round-off scheme, if the SET ARITHABORT option is turned on, an error gets generated in the case of overflow. Loss of only precision and scale isn’t sufficient enough to raise an error. Refer to the example below for a better understanding.

Example: Let us take the example of a college database to understand the syntax better, where understanding DDL, DML, and DCL in SQL helps separate structure definition, data manipulation, and access control. Suppose we want to create a student table with three columns: student id, bus fee, and dues. The dues column is a decimal column, and the bus fee column is a numeric data type, similar to salary DECIMAL(10, 2) or price DECIMAL fields used for exact amounts.

Let us now insert some values into the student table.

Let us now select the details of the student table and see how conversion automatically works.

Output:

Now, as we can see, the conversion has taken place. The number 12345.123 has been converted to 12345.12300 as we have set the precision and scale to 5.

Arithmetic Operations on Numeric Data

The arithmetic operators are used to perform arithmetic operations on the SQL numeric data types. Arithmetic Operators work with binary operands.

The Arithmetic operators can perform all arithmetic operations like addition, subtraction, multiplication, division, and modulus on the operator’s operands. The same arithmetic and comparison logic can also apply to date and time data when the correct temporal type is chosen.

The SQL Comparison Operators are used with the WHERE clause when we need to provide some conditions regarding the operation. Well-formatted statements, often produced using an online SQL formatter and beautifier, make such conditional logic easier to read and debug.

Following is the list of arithmetic operators present in SQL:

Use DATE or TIME instead of DATETIME when only one component is needed, since proper data type selection aligns with normalization rules and avoids unnecessary complexity and also depends on the type of database system in SQL you are using. | Let us take an example to understand the Arithmetic operators in a better way.

Suppose we have a database of employees. The employee table has four columns: employee ID, Salary, Tax, and Bonus.

Overview of the employee table:

Now, let us perform some arithmetic operations using arithmetic operators.

Output:

In this query, we have added the Salary and Bonus amount to calculate the Total Salary of the above table.

Similarly, we can use various other arithmetic operators. If we want to get a tax-free salary.

Output:

For global systems such as a table events log, time data may use date and time data types with a time zone-aware option like TIMESTAMPTZ, which stores timestamps in UTC and adjusts them on retrieval; efficient access then depends on underlying file organization techniques in DBMS.

See Also:

• Online SQL Validator

• Scaler School of Technology industry-ready programs

• About Scaler and its upskilling mission

• Careers at Scaler

• Business careers at Scaler

• Contact Scaler for collaborations

• Scaler Academy career acceleration program