/*********************************
NUMERIC DATA TYPES IN SQL SERVER
**********************************/
/*
Thre are different data types in SQL Server allowing for different possible values and different storage sizes. Also, you have exact data types and approximate data types (float, real)
that don't store the exact number value but a close approximation. There can be some storage and speed benefits to approximate types, however usually in SQL Server DECIMAL or NUMERIC
are used. Apparently you also want to avoid using float or real columns in WHERE clauses.
INT - an integer, values between -2,147,483,648 to 2,147,483,648, 4 byte storage space
TINYINT - an integer, values between 0 to 255, 1 byte storage space
SMALLINT - an integer, values between -32,768 to 32,768, 2 byte storage space
BIGINT - an integer, values between -2E63 + 1 to 2E63 - 1, 8 byte storage space
DECIMAL - a decimal up to 38 digits, values between -10E38 + 1 to 10E38 - 1, 5-17 bytes depending on precision
NUMERIC - functionally equivalent to DECIMAL
FLOAT(with argument 24 or less) - approximate data type where the decimal point can float anywhere (the precision is constant, but the scale can change), values between -3.40E38 to -1.18E-38, 0 , 1.18E-38 to 3.40E38, 4 byte store space
FLOAT(with argument greater than 24 and less than or equal to 53) - approximate data type where the decimal point can float anywhere (the precision is constant, but the scale can change), values between -1.79E308 to -2.23E-308, 0 , -2.23E-308 to 1.79E308, 8 byte store space
REAL - functionally equivalent to FLOAT(24)
MONEY - decimal representing current, values between -922,337,203,685,477.5808 to 922,337,203,685,477.5807, 4 byte storage space
SMALLMONEY - values between -214,748.3648 to 214,748.3647, 4 byte storage space
*/
/*********************************
CONVERTING NUMERIC TYPES
**********************************/
/*
If you insert a numeric value into a numeric column, that value will be recorded according to the column data type. For example, say you enter the following
values into columns of type INT, DECIMAL(18,4), DECIMAL(8,2), NUMERIC(8,2), FLOAT(8) and MONEY:
2
20.30394847
75.333345
822.38397796925
897982.238566892359
You can see how the values will be converted into the data type of the column:
IntegerColumn DecimalColumn4Scale DecimalColumn2Scale NumericColumn FloatColumn8 MoneyColumn
------------- --------------------- -------------------- --------------- ------------- -------------
2 2.0000 2.00 2.00 2 2.00
20 20.3039 20.30 20.30 20.30395 20.3039
75 75.3373 75.34 75.34 75.33734 75.3373
822 822.3840 822.38 822.38 822.384 822.384
897982 897982.2386 897982.24 897982.24 897982.3 897982.2386
*/
/*********************************
INT
**********************************/
/*
When converting a decimal or float to an integer, the number will be truncated (cut off and not rounded).
*/
/*********************************
DECIMAL/NUMERIC
**********************************/
/*
The arguments are DECIMAL(precision[, scale]) and NUMERIC(precision[, scale]). Precision is the total number of digits both to the left and right of the
decimal point, and scale is the number of digits to the right of the decimal point. Scale must therefore have a value between 0 and the precision.
The default precision is 18, and the default scale is 0.
Converting from decimal or numeric to float or real can cause some loss of precision (losing information). Converting from int, smallint, tinyint, float,
real, money, or smallmoney to either decimal or numeric can cause overflow (an error resulting from not being able to fit the number into the parameters for
the data type that you're converting to, such as having an integer number higher than the maximum value for the type INT).
By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT
option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.
*/
/*********************************
FLOAT/REAL
**********************************/
/*
Float is an approximate-data type for use with floating point numeric data which is apparently only to be used when the precision provided by decimal (up to 38 digits) is insufficient.
Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. You can optionally specify an n number, which is the number of bits
that are used to store the mantissa of the float number in scientific notation, and therefore specifies the storage size. This n can be between 1 and 53. The default value of n is 53.
n value Precision Storage Size
------- --------- ------------
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
When float is converted to integer the value is truncated.
*/