1. Data Type Categories

Reference: Data Types

1. Numerics
   1. Exact numerics
   2. Approximate numerics
2. Strings
   1. Character strings
   2. Unicode character strings
   3. Binary strings
3. Date and time
4. Other data types

2. Exact numerics

Reference: int

Reference: money

1. bigint (8 Bytes), int (4 Bytes), smallint (2 Bytes), tinyint (1 Byte) bigint max = 2^63-1, int max = 2^31-1, smallint max = 2^15-1, tinyint max = 255
2. decimal, numeric => can be used interchangeably, for values with fixed precision and scale.
3. bit (0, 1, NULL), stored as 1 Byte string TRUE will be converted to 1 and FALSE will be converted to 0
4. money (8 Bytes) and smallmoney (4 Bytes) The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

Note: when convert from money or float to integer data types, we will lose the precision; when converting to decimal/numeric, we must specify the precision and scale.

DECLARE @mymoney_sm smallmoney = 3148.29;
SELECT   CAST(@mymoney_sm AS int) AS 'SM_MONEY int',  -- 3148
         CAST(@mymoney_sm AS decimal) AS 'SM_MONEY decimal', -- 3148
         CAST(@mymoney_sm AS decimal(10, 2)) AS 'SM_MONEY decimal', -- 3148.29
         CAST(@mymoney_sm AS numeric) AS 'SM_MONEY numeric', -- 3148
         CAST(@mymoney_sm AS numeric(10, 2)) AS 'SM_MONEY numeric', -- 3148.29
         CAST(@mymoney_sm AS float) AS 'SM_MONEY float', -- 3148.29
         CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar'; -- 3148.29

3. Approximate numerics

If you need to store data where small difference does not matter, you can use Float or Real. But if you need to have exact data such as for financial application. You should be using Numeric/Decimal or Money data type to save exact data, as small difference can impact in calculations.

1. float float(n), n between 1 to 53, default n = 53. if (1 <= n <= 24), n is treated as 24, precision is 7 digits, storage size is 4 Bytes; if (25<= n <= 53), n is treated as 53, precision is 15 digits, storage size is 8 Bytes;
2. real: 4 Bytes float real = float(24)

Note: To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.

/** the values for length and decimal parameters to STR should be positive **/
SELECT STR(123.45, 6, 2); -- 123.45
SELECT STR(123.45, 6, 1); -- 123.5
SELECT STR (FLOOR (123.45), 8, 3); -- 123.000
-- When the expression exceeds the specified length, the string returns ** for the specified length.
SELECT STR(123.45, 2, 2);  -- **

/** CAST should be used with STR function if you want to keep the scale **/
SELECT CAST(123.45 AS NVARCHAR(10)); -- 123.45
SELECT CAST(FLOOR (123.45) AS NVARCHAR(10)); -- 123
SELECT CAST(STR(FLOOR (123.45), 6, 2) AS NVARCHAR(10)); -- 123.00

4. Summary

1. Store whole numbers: use int unless the value is going to be more than 2,147,483,647 or less than -2,147,483,648
2. Store number with fraction: use decimal/numeric for exact number or float for approximate number.