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)