This is the 27th day of my participation in the August More Text Challenge

Digital data types include,

  • Used to save decimal floating point numbers real type, float type;
  • Decimal and numeric for precise saving of values;
  • Money /smallmoney;
  • Int, bigINT, smallInt, tinyint;
  • An integer bit with a value of 1, 0, or NULL, often used to indicate the logical true or false.

About floating point numbers

Floating point data in SQL Server is stored in Round up mode. Because floating point numbers are approximations, they sometimes cannot represent values accurately.

Rounding up is the rounding of a non-zero decimal number, regardless of its size, by adding 1 to the least significant bit of the number and carrying the necessary numbers.

The differences between real, float, decimal, numeric

The data type describe storage
real Floating precision numeric data from -3.40e +38 to 3.40E+38 4 bytes
float(n) Float precision numeric data from -1.79e +308 to 1.79E+308. The n parameter indicates whether the field holds 4 bytes or 8 bytes. Float (24) holds 4 bytes, while float(53) holds 8 bytes. The default value is 53. 4 or 8 bytes
decimal(p,s)

numeric(p,s)		 Fixed precision and scale numbers. Allowed from
– 1 0 38 + 1 – 10 ^ {38} + 1 to
1 0 38 – 1 10 ^ {38} – 1 Between the numbers. The p argument specifies the maximum number of digits (to the left and right of the decimal point) that can be stored. P must be a value between 1 and 38. The default value is 18. The s argument specifies the maximum number of digits stored to the right of the decimal point. S must be a value between 0 and p. The default is 0.		 5-17 byte
  • decimalandnumericThe two are synonymous and can be used interchangeably.Used to store values accurately.
  • Float and real: Approximate numeric data type used to represent floating point numeric data. Floating point data is approximate and cannot store exact values. Synonyms of real are float(24)

decimal(numeric)The sample

CREATE TABLE dbo.MyTable  
(  
  MyDecimalColumn DECIMAL(5.2),
  MyNumericColumn NUMERIC(10.5));INSERT INTO dbo.MyTable VALUES (123.12345.12), (12.1234.12); 

SELECT MyDecimalColumn, MyNumericColumn FROM dbo.MyTable; 
Copy the code

The results are as follows:

MyDecimalColumn  MyNumericColumn
123.00	            12345.12000
12.00	            1234.12000
Copy the code

The decimal type treats each particular combination of precision and decimal places as a different data type. For example, decimal(5,5) and decimal(5,0) are treated as different data types.

About real, float logical comparison

Since real and float are approximations, most of the values specified are stored approximations.

Because of the approximation nature, the INTEGER, Decimal, Money, or SmallMoney data types should be used in financial applications, equivalence checks, rounding operations, and so on, where precise values are required.

Use of the = and <> operators in WHERE clause search criteria should be avoided in float or real columns. Float and real columns are best limited to > comparisons or < comparisons.

The IEEE754 specification provides four rounding modes: rounding to nearest, rounding up, rounding down, and rounding to zero.

All values must be exact to a certain precision, but there are small floating point differences. Because the binary representation of floating point numbers can employ any one of many legal rounding rules, it is impossible to quantify floating point values reliably.

Money and smallmoney

A data type that represents a currency or its value.

The Money and SmallMoney data types are accurate to 1/10,000th of the monetary unit they represent. For Informatica, the Money and SmallMoney data types are accurate to one hundredth of the monetary unit they represent.

The data type The scope of storage
money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (for Informatica, it’s -922,337,203,685,477.58 to 922,337,203,685,477.58. Informatica supports only two decimals, not four.) 8 bytes
smallmoney 7-214748364-8 to 214748364 4 bytes

Int, BigInt, SmallInt and tinyInt

An exact numeric data type that uses integer data. To save database space, use the smallest data type that can reliably contain all possible values.

For example, tinyint is sufficient for a person’s age, since no one lives beyond 255. Tinyint is no longer suitable for the age of buildings, which can be more than 255 years old.

The data type The scope of storage
bigint – 2 ^ (63-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). 8 bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 –
tinyint 0 to 255 1 byte

Integer constants greater than 2,147,483,647 are converted to decimal data type instead of BigINT data type. The following example shows that when this threshold is exceeded, the resulting data type changes from int to DECIMAL.

SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;  
Copy the code

Results:

Result1 Result2 1073741824.500000 1073741823Copy the code