SQL Server: data type “rank” in arithmetic operati

问题:

When two values with different data types are put together for an arithmetic operation, SQL Server will convert values automatically into certain data type. E.g.

DECLARE @d NUMERIC(9,6);
SET @d = 1.0;
SELECT @d/3;
GO

results 0.33333333. What is the internal logic behind this conversion? Is there some "rank" between data types (into which "direction" the conversion will happen)?

回答1:

See: Data Type Precedence (for SQL-Server 2000) at msdn.microsoft.com

From the same page for SQL-Server 2008:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

SQL Server uses the following precedence order for data types:

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

For various details regarding when both operands are char, varchar, binary, or varbinary expressions and they are concatenated or compared and when they are both decimals with different precision or scale, see: Precision, Scale, and Length

The following SO question/answer is also relevant: sql-server-truncates-decimal-points-of-a-newly-created-field-in-a-view

回答2:

Since both the numerator and the denominator are integers, the result will be an integer. You need to convert one of the two to a decimal value:

Select @i / 3.0

Data Type Conversion (Database Engine)

CAST and CONVERT (Transact-SQL)

Specifically, note the sections on implicit data type conversion. In your example, since all type are of the same type, there is no conversion. Although the Cast/Convert article is on those functions, it also outlines the implicit conversions.