Asked by:
What data type should I put decimal value?
Question
All replies

Hello,
Best is to use a precise numeric data type: decimal and numeric (TransactSQL)
Olaf Helper
[ Blog] [ Xing] [ MVP] Edited by Olaf HelperMVP, Moderator Friday, January 11, 2019 8:23 AM


Good day Tan,
The documentation explain it very simple and clear.
The first parameter (precision) is "The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point."
This mean that for the number 103.6349816 we need to use at least 10 and for the number 1.2703401666 we need to use at least 11
If you will use less then the number will be rounded!
For the second parameter (scale) the document explains that it is "The number of decimal digits that will be stored to the right of the decimal point."
which mean we will need for the first number to cover this part .6349816 which mean that we need to have at least 7 and for the second number .2703401666 you need at least 10
It seems like your data does not have a consist format. This lead to think that maybe in your case you should use the max values for the DECIMAL parameter which cover all your input, unless you can ensure that you know what is the format of the input data.
* The max value for the Precision is 38 and the max value of the scale is the value of the Precision (since the Precision includes the scale).
For the first number you have 103 before the dot, which mean that you must save at least 3 digit for this part. This man that the Precision should be larger than the Scale by 3.
Best way to find the minimum values of the parameters is to start with the Scale which in your case must be at least 10. Next we need to find the maximum length of the INPUT before the dot (the integer part) which in these two numbers is 3. The Precision must be at least sum of these two length which is 3+10 = 13 in your case
Therefore, For these two specific numbers you can use DECIMAL(13,10)
declare @D DECIMAL(13,10) SET @D = 103.6349816 SELECT @D SET @D = 1.2703401666 SELECT @D
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin]
 Edited by pituachMVP, Moderator Friday, January 11, 2019 12:26 PM
 Proposed as answer by Dedmon DaiMicrosoft contingent staff Monday, January 14, 2019 7:42 AM

If you want to see more examples of how we find the minimum parameters for each number, then you can execute the bellow query :)
;With MyCTE1 AS ( SELECT TOP 1000 p=LEFT(ABS(CHECKSUM(NEWID()))%1000000,(ABS(CHECKSUM(NEWID()))%10 + 1)), s=LEFT(ABS(CHECKSUM(NEWID()))%1000000,(ABS(CHECKSUM(NEWID()))%10 + 1)) FROM sys.all_objects ), MyCTE2 AS ( SELECT [Our Number is] = CONVERT(VARCHAR(10), p)+'.'+CONVERT(VARCHAR(10), s), [Part BEFORE the dot] = CONVERT(VARCHAR(10), p), [length before the dot] = LEN(CONVERT(VARCHAR(10), p)), [Part after the dot] = CONVERT(VARCHAR(10), s), [length AFTER the dot = "Scale"] = LEN(CONVERT(VARCHAR(10), s)), [Length BEFORE + AFTER = "Precision"] = LEN(CONVERT(VARCHAR(10), s)) + LEN(CONVERT(VARCHAR(10), p)) FROM MyCTE1 ) SELECT *, [DECIMAL] = 'DECIMAL(' + CONVERT (VARCHAR(100),[Length BEFORE + AFTER = "Precision"]) + ',' + CONVERT (VARCHAR(100),[length AFTER the dot = "Scale"]) + ')' from MyCTE2
The above query creates 1000 random decimal numbers, and it shows for each number how we find the right parameters of the decimal
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] 
BTW  SQL Decimal() and Numeric() data type are identical.
When you are moving data from one column or variable to another in SQL, and you set your SQL destination as a numeric data type, then the move will fail if any of the source values have nonnumeric data. Your move will also fail if any significant digits are truncated. For example, 123.34 to decimal(4,1) is OK because only the trailing 4 is truncated, but, 123.34 to numeric(4,2) will fail because the leading 1 is truncated.
Now to Excel specifics ... Since you are importing from Excel, what provider are you using: Excel, Access, Flat file, etc? And how are you performing the import? are you using the Import Wizard? I believe that all numeric data will default to float type, since the provider does not want to read the entire Excel sheet to determine the max size of the column data. Just go with float. It will accommodate any size of numeric data and lessen the chances of a failed import due to truncation of data. The only reason float destination type will fail is if your Excel column contains nonnumeric data.

Hello Brenda,
basically I use SQL purpose is for Tableau Software. I only created tables and select the data type and import the csv file into sql. The data that I have csv format and the column data is decimal data type with all kinds of format like 1.310843, 1.296807, 1.439801 ... and another column data is 103.7797, 103.8493, 103,7996 ...
I will try again and put data as FLOAT and see if can import successfully.
I'm not sure if i need to write any script to it.
Regards,
Tan

Hi, Tan_100
>>I will try again and put data as FLOAT and see if can import successfully.
The float data type has a default precision of 15 digits and a storage size of 8 bytes. This means that the maximum number of digits stored to the left and right of the decimal point is 15. There's no guarantee that it will be accurate to a scale of 9. If your data is 1567888.123456789, the result of a float is stored to eight decimal places :1567888.12345679 .
As Ronen Ariely said, You'd better set appropriate precision and scale values for them. It's only when you specify the scale that you can get the data to exactly 9 decimal places.
For more details, please refer to float and real.
Best regards,
Dedmon Dai
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
 Edited by Dedmon DaiMicrosoft contingent staff Monday, January 14, 2019 9:18 AM

Hello Brenda,
basically I use SQL purpose is for Tableau Software. I only created tables and select the data type and import the csv file into sql. The data that I have csv format and the column data is decimal data type with all kinds of format like 1.310843, 1.296807, 1.439801 ... and another column data is 103.7797, 103.8493, 103,7996 ...
I will try again and put data as FLOAT and see if can import successfully.
I'm not sure if i need to write any script to it.
Regards,
Tan
Hi Tan,
Did you read my answer?!?
>> I will try again and put data as FLOAT and see if can import successfully.
FLOAT and DECIMAL ARE TOTALLY DIFFERENT!
I am not saying that using DECIMAL over FLOAT is the right solution for you, since I am not fully familiar with your needs, but I am saying that you should understand the differences in order to chose the right data type. By using FLOAT you might change the original values! Be Careful in your choose :)
Check the documentation for more information. A DECIMAL is an exact numeric value with a fixed precision and a fixed scale. A FLOAT is an Approximatenumber data types or a rounded floating value. For most cases related to human uses (since we think in "DECIMAL world" usually), we should use DECIMAL and not FLOAT by default.
UPDATE: Please check this doc which I quote: Using decimal, float, and real Data
"Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable."
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin]
 Edited by pituachMVP, Moderator Monday, January 14, 2019 11:30 AM

Check this nice Demo:
DECLARE @F1 FLOAT = .35 DECLARE @F2 FLOAT = .45 SELECT ROUND(@F1, 1), ROUND(@F2, 1)  Why the hell the first number @F1 is rounded down, but the second number @F2 is rounded up?!?  Since the first number is not really 0.35 but 0.3499999... behind the scenes :)
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Monday, January 14, 2019 10:49 AM

Tan_100,
My comments about going to Float data type was maybe misguided. I have learned from others smarter than me that "It will accommodate any size data" is incorrect as it will round. You need to verify that what ever type you have setup in a predefined table is large enough to hold your Excel Data. Also, my comments about Excel defaulting to float was based on my testing of importing Excel into tables that were not predefined. Rereading I now believe that your tables are predefined. Sorry for the confusion.

Tan_100,
My comments about going to Float data type was maybe misguided. I have learned from others smarter than me that "It will accommodate any size data" is incorrect as it will round. You need to verify that what ever type you have setup in a predefined table is large enough to hold your Excel Data. Also, my comments about Excel defaulting to float was based on my testing of importing Excel into tables that were not predefined. Rereading I now believe that your tables are predefined. Sorry for the confusion.
Good day TheBrenda,
I highly appreciate when people admit mistakes or something wrong that they did. It is very rare especially in forums. We have a sentence in Hebrew saying that "To make a mistake is human, to admit a mistake is divine". In Hebrew it sounds better because it rhymes ;)
You earn 2 respect points in my book!
And +5 points in the forum :)
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Monday, January 14, 2019 7:13 PM