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

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

使用整數資料的 Exact-number 資料類型。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

RemarksRemarks

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 位於資料類型優先順序圖表中 smallmoneyint 之間。bigint fits between smallmoney and int in the data type precedence chart.

僅當參數運算式的資料類型是 bigint 時,函式才會傳回 bigintFunctions return bigint only if the parameter expression is a bigint data type. SQL ServerSQL Server 不會自動將其他整數類型 (tinyintsmallint,及 int) 提升為 bigintdoes not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

警告

當您使用 +、-、*、/ 或 % 算術運算子,以隱含或明確方式,將 intsmallinttinyintbigint 常數值轉換為 floatrealdecimalnumeric 資料類型時, 在計算運算式結果的資料類型和有效位數時 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 變更為 decimalThe 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  

範例Examples

下列範例會使用 bigintintsmallint,和 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
);  
  
GO  
  
INSERT INTO dbo.MyTable VALUES (9223372036854775807, 2147483647,32767,255);  
 GO  
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

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (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)