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:
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:
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:
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.