Edit

Share via

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

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

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 isn't sufficient for a building's age, because a building can be more than 255 years old.

Data type Range Range expression Storage
bigint -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 -2^63 to 2^63-1 8 bytes
int -2,147,483,648 to 2,147,483,647 -2^31 to 2^31-1 4 bytes
smallint -32,768 to 32,767 -2^15 to 2^15-1 2 bytes
tinyint 0 to 255 2^0-1 to 2^8-1 1 byte

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 doesn't automatically promote other integer data types (tinyint, smallint, and int) to bigint.

Conversion and parameterization

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 isn't autoparameterized, the constant value is first converted to decimal, 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 decimal(1,0), and the constant value 250 is converted to decimal(3,0).

When a query is autoparameterized, the constant value is always converted to decimal(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 isn't autoparameterized, because the results of the autoparameterized query, are truncated to fit into the decimal(10,0) data type.

The tinyint data type isn't supported in Microsoft Fabric.

Convert 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 uses 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's 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's the result set.

MyBigIntColumn       MyIntColumn MySmallIntColumn MyTinyIntColumn
-------------------- ----------- ---------------- ---------------
9223372036854775807  2147483647  32767            255

Related content