Precision, decimal places, and length-msdn

SQL Server 2005 books online (September 2007) Precision, decimal places, and length (TRANSACT-SQL)

Precision is the number of digits in a number. The number of decimal places is the number to the right of the decimal point in the number. For example, the precision of 123.45 is 5, and the number of decimal places is 2.

On SQL Server 2005,NumericAndDecimalThe default maximum precision of the data type is 38. In earlier versions of SQL Server, the default maximum precision is 28.

The length of the numeric data type is the number of bytes occupied by the number. The length of a string or Unicode data type is the number of characters.Binary,VarbinaryAndImageThe length of the data type is the number of bytes. For example,IntThe data type can have 10 digits, which are stored in 4 bytes and do not accept decimal places.IntThe precision of the data type is 10, the length is 4, and the number of decimal places is 0.

When twoChar,Varchar,BinaryOrVarbinaryThe length of the generated expression is the sum of the two source expressions or 8,000 characters.

When twoNcharOrNvarcharThe length of the generated expression is the sum of the two source expressions or 4,000 characters.

When we use Union, distinct T, or intersect to compare two expressions with the same data type but different lengths, the obtained length is the larger length of the two expressions.

BesidesDecimalBeyond the numeric type, the precision and decimal places of the numeric data type are fixed. If an arithmetic operator has two expressions of the same type, the result is of the Data Type and has the precision and decimal places defined for this type. If the operator has two expressions of different numeric data types, the Data Type of the result is determined by the Data Type priority. The result has the precision and decimal places defined for this data type.

The following table defines when the calculation result isDecimalType, how to calculate the accuracy of the result and the number of decimal places. If any of the following conditions is set, the result isDecimal:

• Both expressions areDecimalType.
• An expression isDecimalType, while the other isDecimalData Type with low priority.

The operand expression is expressed by expression E1 (precision: P1, number of decimal places is S1) and expression E2 (precision: P2, number of decimal places is S2. NonDecimalThe precision and decimal places of any expression of the type are the precision and decimal places defined for this expression data type.

Operation	Result Accuracy	Number of decimal places in the result *
E1 + E2	Max (S1, S2) + max (p1-s1, p2-s2) + 1	Max (S1, S2)
E1-E2	Max (S1, S2) + max (p1-s1, p2-s2) + 1	Max (S1, S2)
E1 * E2	P1 + p2 + 1	S1 + S2
E1/E2	P1-S1 + S2 + max (6, S1 + p2 + 1)	Max (6, S1 + p2 + 1)
E1 {Union | except T | intersect} E2	Max (S1, S2) + max (p1-s1, p2-s2)	Max (S1, S2)

* The absolute maximum value of the result precision and decimal places is 38. When the result precision is greater than 38, the corresponding number of decimal places is reduced to avoid the integer part of the result being truncated.