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

Exact-number data types that use integer data.

Data type Range Storage

bigint

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

8 Bytes

int

-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

4 Bytes

smallint

-2^15 (-32,768) to 2^15-1 (32,767)

2 Bytes

tinyint

0 to 255

1 Byte

Remarks

The int data type is the primary integer data type in SQL Server 2005. 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.

Warning

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) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type. For more information about parameterized queries, see Simple Parameterization.

See Also

Reference

ALTER TABLE (Transact-SQL)
CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
Data Types (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
DELETE (Transact-SQL)
INSERT (Transact-SQL)
SET @local\_variable (Transact-SQL)
UPDATE (Transact-SQL)

Other Resources

Data Type Conversion (Database Engine)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

Changed content:
  • Changed the third paragraph of caution note to begin with "When a query is autoparameterized..." In earlier versions, the paragraph begins with "When a query is not autoparameterized..."