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.

Data TypeRange fromRange toMemory
tinyint02551 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.

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

Use select statement to read the values

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

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

Integer Division

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

For Example

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

There are few other ways to achieve the same result

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.

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

But, BigInt variables are not converted to decimal

But, you can covert it to decimal

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)

Read More

  1. Data Types in SQL Server

Leave a Comment

Your email address will not be published.

Scroll to Top