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