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)?
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:
- user-defined data types (highest)
- sql_variant
- xml
- datetimeoffset
- datetime2
- datetime
- smalldatetime
- date
- time
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar (including nvarchar(max) )
- nchar
- varchar (including varchar(max) )
- char
- varbinary (including varbinary(max) )
- 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
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.
