SQL Server Int Data Types tinyint, smallint, int & bigint

SQL Server int (or integer) data types represent mathematical integer numbers or whole numbers. They include both negative & positive numbers. But fractions are not allowed. The SQL Server supports four types of integer data types each of different sizes. They are tinyint, smallint, int & bigInt.

Table of Contents

Range & Size
Creating an Integer column
Converting Values
Choosing the Right Integer Data Type
References

Range & Size

The tinyint, smallint, int & bigInt differ in range & size. The following table shows the max & min value that you can store in each of these fields. It also shows the size in bytes that these data types take in a table.

Data Type	Range from	Range to	Memory
tinyint	0	255	1 byte
smallint	−2^15 (−32,767)	2^15 (−32,768)	2 bytes
int	−2^31 (−2,147, 483,648)	2^31−1 (−2,147, 483,647)	4 bytes
bigint	−2^63 (−9,223,372, 036,854,775,808)	2^63−1 (−9,223,372, 036,854,775,807)	8 bytes

Creating an Integer column

The following example creates a table in SQL Server, with four columns using the tinyint, smallint, int, bigint and data types.

CREATE TABLE intExample (

col_tinyint tinyint,

col_smallint SMALLINT,

col_int INT,

col_bigint bigint

);

We can add a row to the table using the following query.

insert into intExample (col_tinyint,col_smallint,col_int, col_bigint)

values (15,2540,30000,800000000000000)

Use select statement to read the values

select * from intExample

Converting Values

To String

When int data is converted to a string and if the length of the string insufficient to store the number, then SQL Server converts it into *

For Example

SELECT CONVERT(varchar(5),12345) as charNum

Result

12345

While, any number with length more than 5 will convert into *

SELECT CONVERT(varchar(5),123456) as charNum

Results

Integer Division

Dividing the integer by integer, will keep the result as integer. i.e.. you will loose the precision.

For Example

SELECT 21475 / 3

*Result

7158

To get the correct result, convert one of them to decimal as shown below

SELECT 21475 / 3.0

*Result

7158.333333

There are few other ways to achieve the same result

SELECT 21475.0 / 3

SELECT 21475/(CONVERT(DECIMAL(9,2),3))

SELECT 21475/(3 * 1.0)

To Decimal

But if the Integer constants greater than 2,147,483,647 is converted to the decimal data type and not to BigInt.

For Example, the following uses the integer division as explained in the previous section.

SELECT 2147483647 / 3

*Result

715827882

But, here since the number is a above 2,147,483,647, it is converted to decimal

SELECT 2147483648 / 3

*Result

715827882.666666

But, BigInt variables are not converted to decimal

DECLARE @Num BIGINT

SET @Num=2147483648

Select @Num/3

*Result

715827882

But, you can covert it to decimal

DECLARE @Num BIGINT

SET @Num=2147483648

Select @Num/3.0

*Result

715827882.666666

Choosing the Right Integer Data Type

Always use the smallest data type that suits the requirements of the column. For Example for an age, column tinyint is more than sufficient as it can store up to 255.

Smallint can store up to 32,768.

INT is the most commonly used type and should be sufficient for most use cases.

Use BigInt only you expect the column value takes a huge number (i.e., greater than 2,147, 483,647 which is the max value of INT data type). BigInt takes double the space than that of INT