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

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

使用整数数据的精确数字数据类型。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. 例如,对于一个人的年龄,tinyint 就足够了,因为没人活到 255 岁以上。For example, tinyint would be sufficient for a person's age because no one lives to be more than 255 years old. 但对于建筑物的年龄,tinyint 就不再适应,因为建筑物的年龄可能超过 255 年。But tinyint would not be sufficient for a building's age because a building can be more than 255 years old.

数据类型Data type 范围Range 存储器Storage
bigintbigint -2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 字节8 Bytes
intint -2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 个字节4 Bytes
smallintsmallint -2^15 (-32,768) 到 2^15-1 (32,767)-2^15 (-32,768) to 2^15-1 (32,767) 2 字节2 Bytes
tinyinttinyint 0 到 2550 to 255 1 字节1 Byte


int 数据类型是 SQL ServerSQL Server 中的主要整数数据类型 。The int data type is the primary integer data type in SQL ServerSQL Server. bigint 数据类型用于整数值可能超过 int 数据类型支持范围的情况 。The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.

在数据类型优先次序表中,bigint 介于 smallmoney 和 int 之间 。bigint fits between smallmoney and int in the data type precedence chart.

仅当参数表达式为 bigint 数据类型时,函数才返回 bigint 。Functions return bigint only if the parameter expression is a bigint data type. SQL ServerSQL Server 不会自动将其他整数数据类型(tinyint、smallint 和 int)提升到 bigint 。does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.


使用 +、-、*、/ 或 % 等算术运算符将 int、smallint、tinyint 或 bigint 常量值隐式或显式转换为 float、real、decimal 或 numeric 数据类型时,SQL ServerSQL Server 计算数据类型和表达式结果的精度时应用的规则有所不同,这取决于查询是否是自动参数化的 。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 ServerSQL 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. 如果查询不是自动参数化的,则将常量值转换为指定的数据类型之前,首先将其转换为 numeric,该数据类型的精度很大,足以保存常量值 。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. 例如,常量值 1 转换为 numeric (1, 0),常量值 250 转换为 numeric (3, 0) 。For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

如果查询是自动参数化的,则将常量值转换为最终数据类型之前,始终先将其转换为 numeric (10, 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. 例如,包含表达式 SELECT CAST (1.0 / 7 AS float) 的自动参数化查询的结果值将不同于非自动参数化的同一查询的结果值,因为自动参数化查询的结果将被截断以适合 numeric (10, 0) 数据类型 。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 is not autoparameterized, because the results of the autoparameterized query, are truncated to fit into the numeric (10, 0) data type.

转换整数数据Converting integer data

将整数隐式转换为字符数据类型时,如果整数太大而无法容纳到字符字段中,则 SQL ServerSQL Server 会输入 ASCII 字符 42,即星号 (*)。When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL ServerSQL Server enters ASCII character 42, the asterisk (*).

大于 2,147,483,647 的整数常量将转换为 decimal 数据类型,而不是 bigint 数据类型 。Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. 下面的示例显示当超过此阈值时,结果的数据类型将从 int 变为 decimal 。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  


以下示例将使用 bigint、int、smallint 和 tinyint 数据类型创建一个表 。The following example creates a table using the bigint, int, smallint, and tinyint data types. 值插入到每列中并在 SELECT 语句中返回。Values are inserted into each column and returned in the SELECT statement.

CREATE TABLE dbo.MyTable  
  MyBigIntColumn bigint  
,MyIntColumn  int
,MySmallIntColumn smallint
,MyTinyIntColumn tinyint
INSERT INTO dbo.MyTable VALUES (9223372036854775807, 2147483647,32767,255);  
SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn  
FROM dbo.MyTable;  

下面是结果集:Here is the result set.

MyBigIntColumn       MyIntColumn MySmallIntColumn MyTinyIntColumn  
-------------------- ----------- ---------------- ---------------  
9223372036854775807  2147483647  32767            255  
(1 row(s) affected)  

另请参阅See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
SET @local_variable (Transact-SQL)SET @local_variable (Transact-SQL)
sys.types (Transact-SQL)sys.types (Transact-SQL)