decimal 和 numeric (Transact-SQL)decimal and numeric (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

具有固定有效位數和小數位數的數值資料類型。Numeric data types that have fixed precision and scale. Decimal 和 numeric 是同義字,可以交換使用。Decimal and numeric are synonyms and can be used interchangeably.

引數Arguments

decimal[ ( p[ , s] ) ] 和 numeric[ ( p[ , s] ) ]decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
固定有效位數和小數位數的數字。Fixed precision and scale numbers. 當使用最大有效位數時,有效的值是從 - 10^38 +1 到 10^38 - 1。When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. decimal 的 ISO 同義字為 decdec( p, s )The ISO synonyms for decimal are dec and dec(p, s). numeric 在功能上與 decimal 相同。numeric is functionally identical to decimal.

p (有效位數)p (precision)
要儲存的最大小數位數總數。The maximum total number of decimal digits to be stored. 此數目包括小數點的左右兩側。This number includes both the left and the right sides of the decimal point. 有效位數必須是從 1 到最大有效位數 38 的值。The precision must be a value from 1 through the maximum precision of 38. 預設有效位數是 18。The default precision is 18.

注意

Informatica 只支援 16 個有效數字,無論指定的有效位數和小數位數為何。Informatica only supports 16 significant digits, regardless of the precision and scale specified.

s (小數位數)s (scale)
小數點右側所能儲存的小數位數。The number of decimal digits that are stored to the right of the decimal point. 這個數字會從 p 中減去,以判斷小數點左邊的最大位數。This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. 小數位數必須是介於 0 到 p 間的值,且只能在已指定有效位數時指定。Scale must be a value from 0 through p, and can only be specified if precision is specified. 預設小數位數是 0,因此 0 <= s <= pThe default scale is 0 and so 0 <= s <= p. 最大儲存體大小會隨著有效位數而不同。Maximum storage sizes vary, based on the precision.

有效位數Precision 儲存體位元組Storage bytes
1 - 91 - 9 55
10-1910-19 99
20-2820-28 1313
29-3829-38 1717

注意

Informatica (透過 SQL Server PDW Informatica Connector 連線) 只支援 16 個有效數字,無論指定的有效位數和小數位數為何。Informatica (connected through the SQL Server PDW Informatica Connector) only supports 16 significant digits, regardless of the precision and scale specified.

轉換 decimal 與 numeric 資料Converting decimal and numeric data

針對 decimalnumeric 資料類型,SQL ServerSQL Server 會將每個有效位數和小數位數的結合視為不同資料類型。For decimal and numeric data types, SQL ServerSQL Server considers each combination of precision and scale as a different data type. 例如,decimal(5,5)decimal(5,0) 會視為是不同的資料類型。For example, decimal(5,5) and decimal(5,0) are considered different data types.

Transact-SQLTransact-SQL 陳述式中,會使用必要的最小有效位數與小數位數,自動將有小數點的常數轉換成 numeric 資料值。In Transact-SQLTransact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. 例如,常數 12.345 會轉換成有效位數 5、小數位數 3 的 numeric 值。For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

decimalnumeric 轉換成 floatreal 可能會導致有效位數的遺失。Converting from decimal or numeric to float or real can cause some loss of precision. intsmallinttinyintfloatrealmoney,或 smallmoney 轉換成 decimalnumeric 可能會導致溢位。Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

根據預設,SQL ServerSQL Server 在將數字轉換成有效位數與小數位數較小的 decimalnumeric 值時會使用四捨五入。By default, SQL ServerSQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. 相反地,如果 SET ARITHABORT 選項是 ON,SQL ServerSQL Server 會在發生溢位時產生錯誤。Conversely, if the SET ARITHABORT option is ON, SQL ServerSQL Server raises an error when overflow occurs. 只是流失有效位數與小數位數還不足以產生錯誤。Loss of only precision and scale isn't sufficient to raise an error.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 之前,float 值轉換至 decimalnumeric,就會限制為只有 17 個有效位數的值。Prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x), conversion of float values to decimal or numeric is restricted to values of precision 17 digits only. 任何小於 5E-18 (當設定使用 5E-18 科學記號標記法或 0.0000000000000000050000000000000005 十進位標記法時) 的 float 值都會捨去為 0。Any float value less than 5E-18 (when set using either the scientific notation of 5E-18 or the decimal notation of 0.0000000000000000050000000000000005) rounds down to 0. 這不再是 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 的限制。This is no longer a restriction as of SQL Server 2016 (13.x)SQL Server 2016 (13.x).

範例Examples

下列範例會使用 decimalnumeric 資料類型建立資料表。The following example creates a table using the decimal and numeric data types. 值會插入至每個資料行。Values are inserted into each column. 結果會使用 SELECT 陳述式傳回。The results are returned by using a SELECT statement.

CREATE TABLE dbo.MyTable  
(  
  MyDecimalColumn decimal(5,2)  
,MyNumericColumn numeric(10,5)
  
);  
  
GO  
INSERT INTO dbo.MyTable VALUES (123, 12345.12);  
GO  
SELECT MyDecimalColumn, MyNumericColumn  
FROM dbo.MyTable;  
  

以下為結果集:Here is the result set.

MyDecimalColumn                         MyNumericColumn  
--------------------------------------- ---------------------------------------  
123.00                                  12345.12000  
  
(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)