Interview Question of the Week #058 – What is the Difference Among DECIMAL, FLOAT and NUMERIC Datatype?

Question: What is the Difference Between DECIMAL, FLOAT, and NUMERIC Data Types? Answer: Let us learn more about Datatype.

DECIMAL vs. NUMERIC – Datatype

The key difference between DECIMAL and NUMERIC is that there is no difference at all. They are functionally identical and interchangeable. The distinction lies only in their names, which serve as aliases for the same data type.

DECIMAL/NUMERIC vs. FLOAT

The primary distinction between DECIMAL/NUMERIC and FLOAT lies in their precision and representation:

• FLOAT:
  FLOAT is an approximate-number data type. This means it cannot represent all values within its range exactly, as it is designed for scenarios requiring a wide range of values but not necessarily exact precision.
• DECIMAL/NUMERIC:
  These are fixed-precision data types. All values can be represented exactly, based on the defined precision and scale. Precision is the total number of digits, while scale is the number of digits to the right of the decimal point.

Precision and Conversion

When converting between DECIMAL or NUMERIC to FLOAT, there is potential for loss of precision. Additionally, SQL Server treats every combination of precision and scale for DECIMAL or NUMERIC as a unique data type. For instance:

• DECIMAL(2,2) and DECIMAL(2,4) are considered different data types.
  • Example: 11.22 (from DECIMAL(2,2)) and 11.2222 (from DECIMAL(2,4)) are not the same type.

On the other hand, FLOAT handles such differences differently. For example:

• FLOAT(6) considers 11.22 and 11.2222 as the same data type.

Conclusion – Datatype

This question is commonly asked in interviews and tests fundamental knowledge of data types, which is more a general computer science concept than a SQL Server-specific topic. While it’s straightforward, it doesn’t necessarily evaluate a person’s practical SQL skills.

You can connect with me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)