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
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.
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 | insert into intExample (col_tinyint,col_smallint,col_int, col_bigint) values (15,2540,30000,800000000000000) |
Use select statement to read the values
1 2 3 | 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
1 2 3 4 5 6 | SELECT CONVERT(varchar(5),12345) as charNum Result 12345 |
While, any number with length more than 5 will convert into *
1 2 3 4 5 6 | 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
1 2 3 4 5 6 | SELECT 21475 / 3 *Result 7158 |
To get the correct result, convert one of them to decimal as shown below
1 2 3 4 5 6 | SELECT 21475 / 3.0 *Result 7158.333333 |
There are few other ways to achieve the same result
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 | SELECT 2147483647 / 3 *Result 715827882 |
But, here since the number is a above 2,147,483,647, it is converted to decimal
1 2 3 4 5 6 | SELECT 2147483648 / 3 *Result 715827882.666666 |
But, BigInt variables are not converted to decimal
1 2 3 4 5 6 7 8 | DECLARE @Num BIGINT SET @Num=2147483648 Select @Num/3 *Result 715827882 |
But, you can covert it to decimal
1 2 3 4 5 6 7 8 | 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)
Read More
- Data Types in SQL Server
