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)

 

SQL Datatype, SQL Server
Previous Post
Interview Question of the Week #057 – What is GO Statement in SQL SERVER?
Next Post
Interview Question of the Week #059 – What are the Limitations of User Defined Functions (UDF) ?

Related Posts

1 Comment. Leave new

  • Actually in the ANSI ISO standards. This is a little more complicated. There is technically a difference between a float and real because we were defining the standards in the late 1980’s vendors had had various implementations. The IEEE floating-point standards did not exist at the time.

    There is also a difference between numeric (S,P) and decimal (S,P). This had to do with the existence of BCD numbers versus COBOL style picture representations in computer hardware. The decimal data type has to have at least the specified number of decimal places, but it can have more; this was for BCD. Numeric, on the other hand, had to have exactly the specified number of decimal places to match COBOL.

    Microsoft simply picked a subset of the allowable available implementations. This is why numeric and decimal are the same in SQL Server and there is no difference between real and float since the IEEE standards got put into hardware.

    Reply

Leave a Reply