How to Store Decimal Values in SQL Server

Best method for storing a decimal number with varying number of decimal places

Two soutions I can think of:

1. As you've already mentioned, store it in text

2. Store it in decimal(19,6)

   • Store the number of decimals in another field, i.e. 3
   • Use a calculated column to render the format, i.e. FORMAT()

You'd need to reverse engineer the number of decimals though.

This is more complicated but at least you can store the number in a numeric field and have SSMS render what you want.

CREATE TABLE MyTable (
NumericValue DECIMAL(19,6) NOT NULL,
Decimals TINYINT NOT NULL,
Formatted AS (FORMAT(NumericValue,'#.' + LEFT('00000000',Decimals))) 
)

INSERT INTO MyTable (NumericValue, Decimals)
SELECT 10.2,3 UNION ALL
SELECT 1.2,1 UNION ALL
SELECT 1,3

SELECT * FROM MyTable

How does SQL Server store decimal type values internally?

Martin's Smith comment gives you the clue. SQL Server does not use BCD.
It stores the data as a whole number, without the decimal place (which it can do because the decimal place is stored in the metadata for the column). So 5.7456 is stored as 57456, or 0xE070. After SQL's infamous byte swapping this is transformed to 70 E0 00 00.

The leading 01 is the sign. 01 is used for positive numbers; 00 for negatives.

(However, I must ask - why do you need this? In typical use, you should never need to bother with SQL Server's internals)

Save decimal values in SQL Server Management Studio

Several issues here:

1. You need to change the datatype of the [ProductName] field to VARCHAR(50) or some other number if 50 is too few characters for a name. But the text datatype was deprecated when SQL Server 2005 came out and even if you are still on SQL Server 2000, I highly doubt you need more than 8000 characters for a product name ;-). The replacement for TEXT is VARCHAR(MAX), for when a situation actually needs more than 8000 characters.

2. You need to parameterize your query because in the current form, your code is susceptible to SQL Injection.

3. You cannot save decimals in SQL Server Management Studio (SSMS). You can save queries and you can save results, but SSMS is an IDE. You are probably meaning that you cannot save the decimal values to SQL Server using .NET.

4. While not required by an INSERT statement, specifying the column list is always a good idea because the order of the columns might not be what you expect, and if you add columns then this INSERT will fail. Meaning, instead of:

   INSERT INTO dbo.NCAProduct VALUES(...);
   

   do:

   INSERT INTO dbo.NCAProduct
                 (ProductCode, ProductName, SupplierCode, Cost, RetailPrice, Quantity)
   VALUES(...);
   

   Without seeing what values you are trying to insert (which would help narrow down the issue), I would say that this is the most likely cause of the issue, assuming that the order of columns in your initial "/// Columns" list is the actual order. If you look at the order of how the fields are inserted, txtRetail.Text and txtQuantity.Text are switched as the table appears to have Quantity and then RetailPrice. Specifying the field list in the INSERT eliminates this issue, even if it isn't the actual problem here.

If #4 is not the problem, then you need to debug and step through to see the values of txtRetail.Text and txtQuantity.Text as they exist when they are concatenated into the SqlCommand.

Why are my decimal values being rounded to integers in SQL insertions?

You did not define a scale/precision for your decimal. If you want 3 digits after the decimal you should define it as DECIMAL(9,3) which would give you 6 places before the decimal and a decimal of up to 3 places. You need to analyze the expected data and based on what you expect specify the correct precision and scale for your column definition.

CREATE TABLE tmp(
    id int NOT NULL IDENTITY(1,1)PRIMARY KEY,
    toleranceRegion DECIMAL(9,3)
)

See the Sql Server documentation for decimal here.

Why would someone store a DECIMAL as an INT in a SQL Server database and then do the math in the software?

FLOAT is terrible because it has precision and conversion issues, but I think you're more focused on why INT was used instead of DECIMAL or NUMERIC

There are a few possible reasons I can think of:

1. INT is (negligibly) smaller:

   • DECIMAL and NUMERIC are 5-17 bytes depending on precision
   • INT is 4 bytes

2. Some people believe INT is faster, but good luck proving that

3. You mention this is used globally, so it may make more sense for the format of a decimal value to be handled by the front end anyway. For instance, 12.89 in the US is 12,89 in many European countries.

How to store decimal in MySQL?

The first parameter of the DECIMAL declaration is the total digits. You probably want to use DECIMAL (4, 2). This allows for up to two digits before the decimal and two after.

Documentation: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

---

Related Topics