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.

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.

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

References

int, bigint, smallint, and tinyint (Transact-SQL)