August 5, 2003 at 9:47 am
Hi guys
Plz assist me in choosing right data type in a table in SQL Server database.
I need to store values of following kind
12345.67890
1234567890
346656
56.67
Here precision is 10.
Is Decimal or Float data type appropriate for this case ?.
Thanks much
Mack
August 5, 2003 at 10:59 am
Use the float data type for very large or very small values and the decimal data type for values that require exact precision.
Exact Numeric Data Types
Exact numeric data types let you specify exactly the scale and precision to use. For example, you can specify three digits to the right of the decimal and four to the left. A query always returns exactly what you entered. SQL Server supports exact numeric data with two synonymous data types, decimal and numeric, for ANSI compatibility.
Most of the time, you would use exact numeric data types for financial applications in which you want to portray the data consistently (always two decimal places) and to query on that column (for example, to find all loans with an interest rate of 8.75 percent).
Approximate Numeric Data Types
Approximate numeric data types store data as accurately as is possible using binary numbers. For example, the fraction 1/3 is represented in a decimal system as .33333 (repeating). The number cannot be stored accurately, so an approximation is stored.
The approximate numeric data types, real and float, are unable to store all decimal numbers with complete accuracy. Therefore, it is necessary to store an approximation of the decimal number.
Most of the time, you would use approximate numeric data types for scientific or engineering applications.
August 5, 2003 at 12:12 pm
Mark,
Use DECIMAL. But you need to figure out two things.
1. Precision = how many digits (left and right of the decimal point) will you have total?
2. Scale = how many digits will you have to the right of the decimal?
Based on the examples, you will have 15 total with 5 to the right of the decimal.
P S
12345.67890 10 5
1234567890 10 0
346656 6 0
56.67 4 2
So, the MAX you will have to the LEFT of the decimal is 10. The MAX you will have to the RIGHT of the decimal is 5. So PRECISION is 15 (total of both sides) and SCALE is 5 (right side only).
DECIMAL(15,5)
-SQLBill
August 6, 2003 at 9:27 am
With the following sample values you mentioned
=============================================
Plz assist me in choosing right data type in a table in SQL Server database.
I need to store values of following kind
12345.67890
1234567890
346656
56.67
Here precision is 10.
===========================================
To meet the needs of all kinds of data you need a data type decimal with precision of 10 and scale of 5. It can be Decimal (15,5). This will use a 9 byte storage and will always have 5 decimal points for every value.
Refer to SQL BOL if you need more info..
Have a good one
-Sravan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply