Decimal, money, numeric, ints and floats oh my.

When you are choosing a data type to store your numerics, there are some questions you need to ask yourself.
1) Do you want your numeric to remain exact?
2) How precise do you want your numeric to be?
3) How much storage are you willing to give to your data type?


Exactness versus Approximation
There are two types of numerics in SQL Server, Exact numerics and Approximate numerics. Exact numerics garauntee the value will remain the same without losing precision do due rounding. Approximate numerics provide less precision.

There are two types of approximate numerics in SQL Server, float and real.

All other numerics in SQL Server are exact: decimal, number, money, small money, big int, int, small int, tiny int and bit.

If you are storing numerics for financial purposes, you would use an exact numeric data type.

Below is a link on approximate data types in SQL Server, specifically, float. http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/10/26/do-you-use-the-float-datatype.aspx

Precision & Scale
The decimal and numeric data types have a fixed precision and scale. I want to quickly mention that there is no difference between the decimal and numeric data types.

Precision is ther number of digits that can be stored to the left and right of the decimal point. Scale is the number of digits that can be stored to the right of the decimal.

If you need scale, then you would not use any of the whole number data types (big, int, small, tiny, bit).

Both the money and small money data types provide scale to 4 decimal places. If you need further digit representation beyond a scale of 4, you should use either decimal or numeric.

Storage
When planning for very large databases, you need to carefully choose the kinds of data types to use to store your numerics.

decimal and numeric can store up to 17 bytes
float 8 bytes
real 4 bytes
money 8 bytes
small money 4 bytes
bit int 8 bytes
int 4 bytes
small int 2 bytes
tiny int 1 byte
bit (1-8 bits = 1 byte)
Enjoy!

Data Type Conversion Chart

Below is a helpful data type conversion chart. I did not create this, but I wanted to share it because it is very useful. The chart and full article can be found @ http://msdn.microsoft.com/en-us/library/ms187928.aspx