T-SQL int equivalents of decimal/numeric types
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.
|Decimal precision||Storage bytes|
Contrast this with the requirements for the 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.
|Decimal precision||Integer equivalent||Bytes saved|
If you do this, be very careful that you are not inviting overflow errors into your application.