SQL DECIMAL Vs MONEY Datatype
One of our devs today suggested that we use the data type
MONEY for a currency column. Instead of money, I proposed that we use
DECIMAL (19, 4).
Although MONEY has a similar definition, it should be utilised with caution.
SQL MONEY Datatype
currency values are stored in the MONEY data type .
MONEY more closely reflects the real-world situation, in which each figure is calculated to the closest cent, then the average is rounded again.
In a long calculating chain, the discrepancy can add up to a lot more than one cent.
Despite the fact that the MONEY datatype in SQL server saves a lot of space when compared to the DECIMAL(19,4) datatype, there's no need to utilise it for a T-SQL variable in a stored procedure, batch, or function.
Money requires 4 to 8 bytes, whereas decimal numbers can be 5, 9, 13, or 17.
The 9 bytes can cover the same range as the money's 8 bytes.
Example: Let's look at Work table with a MONEY column.
CREATE TABLE Product ( Id INT IDENTITY, ProductName VARCHAR(100), Price MONEY ); INSERT INTO Product VALUES (1,'Realme' 369500000); INSERT INTO Product VALUES (2,'Iphone, 235000000); INSERT INTO Product VALUES (3,'HP Laptop',10200000); SELECT * FROM Product;
Ouput: The output will be
SQL DECIMAL Datatype
The DECIMAL data type is a fixed precision and scale exact number.
The total number of digits allowed in a column is represented by precision, which is an integer.
The number of decimal places is represented by scale, which is likewise an integer value.
Example : We have A table Employee with a DECIMAL column.
CREATE TABLE DemoTable ( Id INT IDENTITY, EmployeeName VARCHAR(100), Salary DECIMAL(10,2) ); GO INSERT INTO Employee VALUES (1,'Smithi', 3450.2); INSERT INTO Employee VALUES (3,'Johnson', 1674.667); INSERT INTO Employee VALUES (5,'Jancy Lopez', 729.352); SELECT * FROM Employee;
Employee salary can hold up to 10 digits and 2 decimal places.
If the value is more than 2 decimal places, it will be rounded.
If the value is less than 2 decimal places, a zero is added to complete the 2 digits.
Output: The output of Decimal datatype
SQL Difference Between MONEY And DECIMAL Datatype
In below table we distinguish between the data types of money & decimal:-
|A monetary value is represented by money.||Decimal is a precision and scale type with a fixed precision.|
|Range of values: -922,337,203,685,477.5808 to 922,337,203,685,477.5807||Values range from 10^38 +1 to 10^38|
|MONEY occupies 8 bytes.||DECIMAL(19,4) occupies 9 bytes.|
|The dollar sign and commas are not a problem for MONEY.||The dollar sign and commas are not supported by DECIMAL.|
|If the fractional units are greater than 5 decimal places, the MONEY data type contains rounding error.||This isn't a rounding error.|