int, bigint, smallint, and tinyint (Transact-SQL)
Exact-number data types that use integer data.
|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|
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.
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
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
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, 214483647,32767,255); GO SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn FROM dbo.MyTable;
Here is the result set.
MyBigIntColumn MyIntColumn MySmallIntColumn MyTinyIntColumn -------------------- ----------- ---------------- --------------- 9223372036854775807 214483647 32767 255 (1 row(s) affected)