CODEWITHSUNDEEP
sql-serverdecimalprecision
Aidan Ryan
Aidan Ryan

Reputation: 11589

Store and return decimals with user-entered precision in SQL Server

Does the SQL Server decimal data type stored the actual number of digits after the decimal for each value in a column?

For example

UPDATE [Mixes] SET [Concentration] = 0.0012500 WHERE [Id] = 1

where

Concentration Decimal(21,11)

Is it possible to retrieve the exact "0.0012500" value as entered by the user, or is it necessary to store the actual number of decimal places entered in another column?

Upvotes: 0

Views: 265

Answers (3)

Cade Roux
Cade Roux

Reputation: 89661

You would not be able to tell the difference between 0.0012500 and 0.00125 after it was entered just using a decimal column. You would need to store in a textual form or parse the information about how it was entered and store that information.

Upvotes: 1

user121301
user121301

Reputation:

The decimal type stores the numeric value up to the precision specified. It does not store formatting. It's like saving an int with leading zeros. The leading zeros don't change the numeric value. If the user-entered format is needed, you'll need to store that separately or store the number as a varchar.

Upvotes: 2

Jonas Lincoln
Jonas Lincoln

Reputation: 9767

You'd have to store the number of decimals separately.

Upvotes: 1

Related Questions