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

An exact numeric data type that uses integer data.

Data type	Range	Store
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 bytes

notes int data type is the primary integer data type in SQL Server." The >int data type is the primary integer data type in SQL Server. bigint data type is intended for use when an integer values might exceed the range that's supported by the int data type. " > bigint data type is used for integer values that may exceed int data type support range.

bigint fits between smallmoney and int in the The data type precedence chart. In the Data type prioritization table, bigint between smallmoney and int.

The function returns bigint only if the parameter expression is a bigint data type . SQL Server does not automatically elevate other integer data types (tinyint,smallint, and int) to bigint.

Note

Use the + 、-、 *,/, or% arithmetic operators towhen an int,smallint,tinyint, or bigint constant value is implicitly or explicitly converted to a float,Real,decimal, or numeric data type, the SQL The rules that are applied when the Server calculates the precision of the data type and expression results vary, depending on whether the query is automatically parameterized. Therefore, similar expressions in a query can sometimes produce different results.if the query is not automatically parameterized, the constant value is converted to numeric before it is converted to the specified data type, and the data type is large enough to hold the value of the constant. For example, a constant value of 1 is converted to numeric (1, 0), and a constant value of 250 is converted to numeric (3, 0). If the query is automatically parameterized, the constant value is always converted to the final data type before it is converted to theNumeric (10, 0). if the/operator is involved, then for similar queries, not only the precision of the result type may be different, but the resulting value may also be different. For example, an automatic parameterized query that contains an expression SELECT CAST (1.0/7 as float) will have a different result value than the result value of the same query that is not automatically parameterized, because the results of an automatic parameterized query will be truncated to fit the numeric (10, 0) data Type.

Converting integer data

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

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

SELECT 2147483647 / 2  as 2147483649 / 2  as RESULT2;

Here is the result set:

RESULT1      Result21073741823   1073741824.500000

Example

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 TABLEdbo. MyTable (Mybigintcolumnbigint, Myintcolumnint, Mysmallintcolumnsmallint, Mytinyintcolumntinyint);GOINSERT  intoDbo. MyTableVALUES(9223372036854775807,214483647,32767,255); GOSELECTMybigintcolumn, Myintcolumn, Mysmallintcolumn, Mytinyintcolumn fromDbo. MyTable;

Here is the result set:

Mybigintcolumn       Myintcolumn mysmallintcolumn mytinyintcolumn-------------------------------------------------- ------------9223372036854775807  214483647   32767            255 (1 row (s) affected)

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