T-SQL INT equivalents of Decimal Numeric
The DECIMAL type in T-SQL is for storing fixed precision numbers. You can define the total number of digits to store (the precision), as well as how many digits are to the right of the decimal point (the scale)
eg. to store a number in the range -9.9 to 9.9 (with 0.1 increments), you could use decimal(2, 1).
Sometimes database systems may store integer values using their equivalent of the decimal type (with a scale of zero), and when you use SSIS to import the data, it just defaults to creating a compatible schema.
There are some storage and potential performance benefits to using integer data types, so it may be worth checking whether the source data would actually fit inside a native int (or tinyint or bigint).
First, let’s look at the storage requirements for decimal types. As you can see from Table 1, even a decimal(1,0) will still take up 5 bytes.
Table 1 - Bytes required to store decimal precision types
| Decimal precision | Storage bytes |
|---|---|
| 1-9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
Contrast this with the requirements for the integer types.
Table 2 - Bytes required to store integer types
| Data type | Range | Storage bytes |
|---|---|---|
| tinyint | 0 to 255 | 1 |
| smallint | -215 (-32,768) to 215-1 (32,767) | 2 |
| int | -231 (-2,147,483,648) to 231-1 (2,147,483,647) | 4 |
| bigint | -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807) | 8 |
So for certain ranges of precision, the value can safely be stored in an equivalent integer type.
Table 3 - Compatible integer types
| Decimal precision | Integer equivalent | Bytes saved |
|---|---|---|
| 1-2 | tinyint | 4 |
| 3-4 | smallint | 3 |
| 5-9 | int | 1 |
| 10-18 | bigint | 1 |
If you do this, be very careful that you are not inviting overflow errors into your application.