What is the Difference Between Numeric, Float, and Decimal in SQL server?

The SQL Server has a variety of data types to store data. These data types can accompany several forms like string, integer, double, date, time, etc. Therefore selecting the appropriate data structure is crucial for maintaining the data accuracy and optimizing the system’s performance.

This article delves into the fundamentals of three crucial data types: Numeric, Float, and Decimal.

Numeric Data Type

The Numeric data type is used to represent a number with fixed precision and fixed scale. This means, there is a defined precision for numeric data type such that if we try to store a number that is out of bounds, the numeric data type will return an error.

Syntax:

NUMERIC(precision, scale)

Numeric Data Type Example

-- Create the table
CREATE TABLE STUDENT(
    Name VARCHAR(20),
    Result NUMERIC(5,2)
);

-- Insert sample data
INSERT INTO STUDENT(Name, Result) VALUES ('John', 98.75);
INSERT INTO STUDENT(Name, Result) VALUES ('Alice', 87.50);

Output:

Numeric output

Explanation: The SQL script creates a table named “STUDENT” with columns “Name” and “Result” of data type NUMERIC(5,2), allowing for fixed precision numbers with 5 total digits and 2 decimal places. Sample data is inserted with the names “John” and “Alice” along with their corresponding results, where precision is enforced.

Float Data Type

The Float data type is used to store floating point numbers that are not fixed/exact. This means that the float data type does not store the exact values, rather, it stores the approximate value of the stored number.

Syntax:

FLOAT(precision)

Float Data Type Example

-- Create the table
CREATE TABLE STUDENT(
    Name VARCHAR(20),
    Result FLOAT(5)
);

-- Insert sample data
INSERT INTO STUDENT(Name, Result) VALUES ('John', 98.75000);
INSERT INTO STUDENT(Name, Result) VALUES ('Alice', 87.50000);

Output:

Flatt output

Explanation: The SQL script creates a table named “STUDENT” with columns “Name” and “Result” of data type FLOAT(5). Sample data is inserted into the table with names “John” and “Alice” along with their corresponding results. The FLOAT(5) data type allows for storing approximate floating-point numbers with up to 5 digits.

Decimal Data Type

The Decimal data type is exactly similar to the numeric data type. Both have fixed precision exact values. Both have the same syntax and the same memory usage.

Syntax:

DECIMAL(precision, scale)

Decimal Data Type Example

-- Create the table
CREATE TABLE STUDENT(
    Name VARCHAR(20),
    Result DECIMAL(5, 2)
);

-- Insert sample data
INSERT INTO STUDENT(Name, Result) VALUES ('John', 98.75);
INSERT INTO STUDENT(Name, Result) VALUES ('Alice', 87.50);

Output:

Decimal output

Explanation: The SQL script creates a table named “STUDENT” with columns “Name” and “Result” of data type DECIMAL(5,2), enforcing fixed precision numbers with 5 total digits and 2 decimal places. Sample data is inserted for students “John” and “Alice” with their respective results, adhering to the specified precision.

Benefits of Using Numeric, Float, and Decimal in SQL Server

• Numeric: Offers strict precision for exact numeric values, suitable for financial calculations.
• Float: Provides efficient storage for approximate floating-point numbers, ideal for scientific computations.
• Decimal: Combines fixed precision with adjustable scale, offering accuracy with flexibility, suitable for diverse applications.

Each type has distinct advantages, catering to specific requirements in SQL data storage and manipulation.

Difference Between Numeric, Float, and Decimal in SQL server

Parameters	Numeric	Float	Decimal
Size (in Bytes)	5, 9, 13, 17 (based on precision)	4, 8 (based on precision)	5, 9, 13, 17 (based on precision)
Scale	Defined by user	Not required	Defined by user
Precision	Fixed precision (strict precision)	Approximate precision	Fixed precision (adjustable precision)
Range	-10^38 to 10^38	-1.79 x 10^308 to 1.79 x 10^308	-10^38 to 10^38
Exact Values	Yes	No	Yes
Storage Efficiency	Less efficient	More efficient	Less efficient

Conclusion

All 3 data types have their advantages. However, the numeric and decimal data types have been considered the same since the 2016 update of SQL. The only notable difference between them remains the strictness and out-of-bound error in numeric type. One of the biggest differences between float and numeric/decimal lies in the fact that float stores the approximate value whereas the decimal and numeric have fixed point precision values. Based on the parameters passed to these data types, their sizes vary accordingly.