有效位數、小數位數和長度 (Transact-SQL)Precision, scale, and Length (Transact-SQL)

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

位數 (Precision) 是指數字中總共的位數。Precision is the number of digits in a number. 小數位數 (Scale) 則是指數字中小數點右方的位數。Scale is the number of digits to the right of the decimal point in a number. 例如 123.45 的位數是 5,小數位數是 2。For example, the number 123.45 has a precision of 5 and a scale of 2.

SQL ServerSQL Server 中,numericdecimal 資料類型的預設最大有效位數為 38。In SQL ServerSQL Server, the default maximum precision of numeric and decimal data types is 38. 在舊版的 SQL ServerSQL Server 中,預設的最大值是 28。In earlier versions of SQL ServerSQL Server, the default maximum is 28.

數值資料類型的長度是用來儲存數字的位元組數目。Length for a numeric data type is the number of bytes that are used to store the number. 若為 varchar 和 cha,則其字元字串的長度即是位元組的數目。For varchar and char, the length of a character string is the number of bytes. 若為 nvarchar 和 nchar,則其字元字串的長度即是位元組配對的數目。For nvarchar and nchar, the length of the character string is the number of byte-pairs. binaryvarbinaryimage 資料類型的長度為位元組的數目。The length for binary, varbinary, and image data types is the number of bytes. 例如,int 資料類型可以保留 10 位數,儲存在 4 個位元組中且不接受小數點。For example, an int data type can hold 10 digits, is stored in 4 bytes, and doesn't accept decimal points. int 資料類型的有效位數是 10,長度是 4,小數位數是 0。The int data type has a precision of 10, a length of 4, and a scale of 0.

當串連兩個 charvarcharbinaryvarbinary 運算式時,所產生運算式長度是兩個來源運算式長度的總和,最多為 8,000 個位元組。When concatenating two char, varchar, binary, or varbinary expressions, the length of the resulting expression is the sum of the lengths of the two source expressions, up to 8,000 bytes.

當串連兩個 ncharnvarchar 運算式時,所產生運算式長度是兩個來源運算式長度的總和,最多為 4,000 個位元組配對。When concatenating two nchar or nvarchar expressions, the length of the resulting expression is the sum of the lengths of the two source expressions, up to 4,000 byte-pairs.

使用 UNION、EXCEPT 或 INTERSECT 來比較資料類型相同但長度不同的兩個運算式時,所產生長度是兩個運算式中較長的長度。When comparing two expressions of the same data type but different lengths by using UNION, EXCEPT, or INTERSECT, the resulting length is the longer of the two expressions.

decimal 兩旁數值資料類型的有效位數和小數位數是固定的。The precision and scale of the numeric data types besides decimal are fixed. 當算術運算子有兩個相同類型的運算式時,結果會有相同的資料類型,並具有定義給這個類型的有效位數和小數位數。When an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type. 如果運算子有兩個含不同數值資料類型的運算式,資料類型優先順序的規則會定義這個結果的資料類型。If an operator has two expressions with different numeric data types, the rules of data type precedence define the data type of the result. 結果會有定義給它的資料類型的有效位數和小數位數。The result has the precision and scale defined for its data type.

下表定義在運算結果是 decimal 類型時,如何計算結果的有效位數和小數位數。The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. 若符合下列其中一項條件,結果為 decimalThe result is decimal when either:

  • 兩個運算式都是 decimalBoth expressions are decimal.
  • 一個運算式為 decimal,另一個運算式為優先順序低於 decimal 的資料類型。One expression is decimal and the other is a data type with a lower precedence than decimal.

運算元運算式表示成運算式 e1 (有效位數是 p1,小數位數是 s1) 和運算式 e2 (有效位數是 p2,小數位數是 s2)。The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. 任何不是 decimal 之運算式的有效位數和小數位數,都是定義給運算式資料類型的有效位數和小數位數。The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression. 函式 max(a,b) 的意思是:取 "a" 或 "b" 當中較大的值。The function max(a,b) means the following: take the greater value of "a" or "b". 同樣地,min(a,b) 表示取 "a" 或 "b" 當中較小的值。Similarly, min(a,b) indicates to take the smaller value of "a" or "b".

作業Operation 結果有效位數Result precision 結果小數位數 *Result scale *
e1 + e2e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)max(s1, s2)
e1 - e2e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) + 1max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)max(s1, s2)
e1 * e2e1 * e2 p1 + p2 + 1p1 + p2 + 1 s1 + s2s1 + s2
e1 / e2e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1)p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2)max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)max(s1, s2)
e1 % e2e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 )min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)max(s1, s2)

* 結果有效位數及小數位數的絕對最大值為 38。* The result precision and scale have an absolute maximum of 38. 當結果有效位數大於 38 時,會縮小至 38,並會縮減對應的小數位數,以防止截斷結果的整數部分。When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. 在乘法或除法等某些案例中,為保持十進位有效位數,將不會縮減小數位數因數 (雖然可能會引發溢位錯誤)。In some cases such as multiplication or division, scale factor won't be reduced, to maintain decimal precision, although the overflow error can be raised.

在加法跟減法運算中,我們需要 max(p1 - s1, p2 - s2) 位置來儲存十進位數字的整數部分。In addition and subtraction operations, we need max(p1 - s1, p2 - s2) places to store integral part of the decimal number. 若沒有足夠的空間來儲存它們 (即 max(p1 - s1, p2 - s2) < min(38, precision) - scale) 時,便會縮減小數位數,為整數部分提供足夠的空間。If there isn't enough space to store them that is, max(p1 - s1, p2 - s2) < min(38, precision) - scale, the scale is reduced to provide enough space for integral part. 其結果的小數位數便是 MIN(precision, 38) - max(p1 - s1, p2 - s2),因此小數部分可能會四捨五入以調整至符合結果的小數位數。Resulting scale is MIN(precision, 38) - max(p1 - s1, p2 - s2), so the fractional part might be rounded to fit into the resulting scale.

在乘法及除法運算中,我們需要 precision - scale 位置來儲存結果的整數部分。In multiplication and division operations, we need precision - scale places to store the integral part of the result. 可能會使用下列規則來縮小小數位數:The scale might be reduced using the following rules:

  1. 若整數部分小於 32,則結果的小數位數會縮減至 min(scale, 38 - (precision-scale)),因為它不可大於 38 - (precision-scale)The resulting scale is reduced to min(scale, 38 - (precision-scale)) if the integral part is less than 32, because it can't be greater than 38 - (precision-scale). 在此案例中,結果可能會四捨五入。Result might be rounded in this case.
  2. 若小數位數小於 6 且整數部分大於 32,則小數位數便不會變更。The scale won't be changed if it's less than 6 and if the integral part is greater than 32. 在此案例中,若無法調整為符合 decimal(38, scale),便可能會引發溢位錯誤In this case, overflow error might be raised if it can't fit into decimal(38, scale)
  3. 若小數位數大於 6 且整數部分大於 32,則小數位數便會設為 6。The scale will be set to 6 if it's greater than 6 and if the integral part is greater than 32. 在此案例中,整數部分及小數位數都會縮小,且其結果的類型為 decimal(38, 6)。In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). 結果可能會四捨五入至 6 個小數位數,或是擲回溢位錯誤 (若整數部分無法調整至 32 位數)。Result might be rounded to 6 decimal places or the overflow error will be thrown if the integral part can't fit into 32 digits.

範例Examples

下列運算式會傳回結果 0.00000090000000000 且不四捨五入,因為結果可調整至符合 decimal(38,17)The following expression returns result 0.00000090000000000 without rounding, because result can fit into decimal(38,17):

select cast(0.0000009000 as decimal(30,20)) * cast(1.0000000000 as decimal(30,20)) [decimal 38,17]

在此案例中,位數 (precision) 為 61,小數位數 (scale) 為 40。In this case precision is 61, and scale is 40. 整數部分 (precision-scale = 21) 小於 32,因此這個案例屬於乘法規則中的案例 (1),小數位數會計算為 min(scale, 38 - (precision-scale)) = min(40, 38 - (61-40)) = 17Integral part (precision-scale = 21) is less than 32, so this case is case (1) in multiplication rules and scale is calculated as min(scale, 38 - (precision-scale)) = min(40, 38 - (61-40)) = 17. 結果類型為 decimal(38,17)Result type is decimal(38,17).

下列運算式會傳回結果 0.000001 以符合 decimal(38,6)The following expression returns result 0.000001 to fit into decimal(38,6):

select cast(0.0000009000 as decimal(30,10)) * cast(1.0000000000 as decimal(30,10)) [decimal(38, 6)]

在此案例中,位數為 61,小數位數為 20。In this case precision is 61, and scale is 20. 小數位數大於 6 且整數位數 (precision-scale = 41) 大於 32。Scale is greater than 6 and integral part (precision-scale = 41) is greater than 32. 這個案例屬於乘法規則中的案例 (3),其結果類型為 decimal(38,6)This case is case (3) in multiplication rules and result type is decimal(38,6).

另請參閱See also

運算式 (Transact-SQL)Expressions (Transact-SQL)
資料類型 (Transact-SQL)Data Types (Transact-SQL)