numeric data type sql

numeric data type sql
decimal and numeric (Transact-SQL)

7/23/2017

5 minutes to peruse

Givers

Ed Macauley olprod OpenLocalizationService

THIS TOPIC APPLIES TO: simSQL Server (starting at 2008) yes Microsoft AzuresimAzure SQL DataBank SQL Data Warehouse simParallel Data Warehouse

Numeric information writes that have settled exactness and scale. Decimal and numeric are equivalent words and can be utilized conversely.

Contentions

decimal [(p [, s])] and numeric [(p [, s])]

Settled exactness and scale numbers. At the point when most extreme accuracy is utilized, the legitimate esteems ​​will be - 10 ^ 38 +1 to 10 ^ 38 - 1. The ISO equivalent words for decimal are ten and dec (p, s). numeric is practically comparable to decimal.

p (exactness)

The most extreme aggregate number of decimal digits that can be put away to one side and right of the decimal point. The exactness must be an incentive from 1 to the most extreme accuracy of 38. The default exactness is 18.

Note

Informatica is good just with 16 noteworthy digits, paying little heed to the exactness and scale indicated.

s (scale)

The most extreme number of decimal digits that can be put away to one side of the decimal point. This number is subtracted from p to decide the most extreme number of digits to one side of the decimal separator. The greatest number of decimal digits that can be put away to one side of the decimal point. The scale ought to be an incentive from 0 to p. The scale must be indicated if exactness is as well. The default scale is 0; along these lines, 0 <= s <= p. Most extreme stockpiling sizes change, in light of exactness.

Exactness Storage Bytes

1 - 9 5

10-19 9

20-28 13

29-38 17

Note

Informatica (associated through the SQL Server PDW Informatica connector) is perfect just with 16 critical digits, paying little respect to the accuracy and scale determined.

Changing over decimal and numeric information

For both decimal and numeric information writes, SQL Server considers every particular blend of exactness and scaling as an alternate information compose. For instance, decimal (5.5) and decimal (5.0) are viewed as various information writes.

In Transact-SQL explanations, a steady with a decimal point is consequently changed over to a numeric information esteem, utilizing the required least scale and accuracy. For instance, the consistent 12,345 is changed over to a numeric incentive with a precision of 5 and a size of 3.

Changing over from decimal or numeric to buoy or genuine can cause loss of exactness. Changing over from int, smallint, tinyint, glide, genuine, cash or smallmoney to decimal or numeric can cause flood.

As a matter of course, SQL Server utilizes adjusting while changing over a number to a decimal or numeric incentive with bring down accuracy and scale. Be that as it may, if the SET ARITHABORT choice is ON, SQL Server will produce a blunder when the flood happens. Simply the loss of exactness and scale isn't sufficient to create a blunder.

While changing over genuine or gliding esteems ​​to decimal or numeric esteems, the decimal esteem will never surpass 17 decimals. Any coasting esteem <5E-18 will dependably be changed over to 0.

Illustrations

The accompanying illustration makes a table utilizing the decimal and numeric information composes. The qualities ​​are entered in every section and the outcomes are returned utilizing a SELECT proclamation.