SQL Server INT

Summary: in this tutorial, you will learn how about the integer data types and how to use them effectively to store integer values in the database.

SQL Server support standard SQL integer types including BIGINT , INT , SMALLINT , and TINYINT . The following table illustrates the range and storage of each integer type:

It is a good practice to use the smallest integer data type that can reliably contain all possible values. For example, to store the number of children in a family, TINYINT is sufficient because nowadays no one could have more than 255 children. However, TINYINT is would not be sufficient for storing the stories of a building because a building can have more than 255 stories.

SQL Server Integers example

The following statement creates a new table that consists of four integer columns:

CREATE TABLE test.sql_server_integers ( bigint_col bigint, int_col INT, smallint_col SMALLINT, tinyint_col tinyint );Code language: SQL (Structured Query Language) (sql)

The following INSERT statement adds the maximum integers of BIGINT , INT , SMALLINT , and TINYINT to the corresponding columns of the table:

INSERT INTO test.sql_server_integers ( bigint_col, int_col, smallint_col, tinyint_col ) VALUES ( 9223372036854775807, 2147483647, 32767, 255 );Code language: SQL (Structured Query Language) (sql)

To show the values stored in the test.sql_server_integers table, you use the following SELECT statement:

SELECT bigint_col, int_col, smallint_col, tinyint_col FROM test.sql_server_integers;Code language: SQL (Structured Query Language) (sql)

Converting integer data

SQL Server converts the integer constant greater than 2,147,483,647 to DECIMAL data type, not BIGINT data type as shown in the following example:

SELECT 2147483647 / 3 AS r1, 2147483649 / 3 AS r2;Code language: SQL (Structured Query Language) (sql)

The query example showed when the threshold value was exceeded, the data type of the result changed from INT to a DECIMAL .

In this tutorial, you have learned various SQL Server integer data types and how to use them to store integers in the database.

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

Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person’s age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building’s age because a building can be more than 255 years old.

Remarks

The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.

bigint fits between smallmoney and int in the data type precedence chart.

Functions return bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type’s precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) , differs from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query, are truncated to fit into the numeric (10, 0) data type.

The tinyint data type is not supported in Microsoft Fabric.

Converting integer data

When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).

Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. The following example shows that when the threshold value is exceeded, the data type of the result changes from an int to a decimal.

SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ; 

Here is the result set.

Result1 Result2 1073741823 1073741824.500000 

Examples

The following example creates a table using the bigint, int, smallint, and tinyint data types. Values are inserted into each column and returned in the SELECT statement.

CREATE TABLE dbo.MyTable ( MyBigIntColumn BIGINT ,MyIntColumn INT ,MySmallIntColumn SMALLINT ,MyTinyIntColumn TINYINT ); GO INSERT INTO dbo.MyTable VALUES (9223372036854775807, 2147483647,32767,255); GO SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn FROM dbo.MyTable; 

Here is the result set.

MyBigIntColumn MyIntColumn MySmallIntColumn MyTinyIntColumn -------------------- ----------- ---------------- --------------- 9223372036854775807 2147483647 32767 255 (1 row(s) affected) 

SQL TINYINT Data Type

The TINYINT data type is an integer value from 0 to 255.

TINYINT is the smallest integer data type and only uses 1 byte of storage.

An example usage of TINYINT is a person’s age since no person reaches the age of 255.

Example

A table with a TINYINT column.

CREATE TABLE DemoTable ( Id INT IDENTITY, Name VARCHAR(100), Age TINYINT ); GO INSERT INTO DemoTable VALUES ('Anna', 32); INSERT INTO DemoTable VALUES ('Carlos', 19); INSERT INTO DemoTable VALUES ('Marlon', 55); INSERT INTO DemoTable VALUES ('Kelly', 41); INSERT INTO DemoTable VALUES ('Martha', NULL); GO SELECT * FROM DemoTable; GO DROP TABLE DemoTable; GO 

More Examples

TINYINT with OTHER INT DATA TYPES

Problem: List the maximum value of each integer data type.

CREATE TABLE DemoTable ( MyBigInt BIGINT, MyInt INT, MySmallInt SMALLINT, MyTinyInt TINYINT ); GO INSERT INTO DemoTable VALUES (9223372036854775807, 2147483647, 32767, 255); GO SELECT * FROM DemoTable; GO DROP TABLE DemoTable; GO