T-SQL int equivalents of decimal/numeric types

Written on January 17, 2011

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	-2¹⁵ (-32,768) to 2¹⁵-1 (32,767)	2
int	-2³¹ (-2,147,483,648) to 2³¹-1 (2,147,483,647)	4
bigint	-2⁶³ (-9,223,372,036,854,775,808) to 2⁶³-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.

Categories: SQL