What Happens to an Arithmetic Operation with Two Different Data Types in SQL Server Transact-SQL?

When two expressions with different data types are put together for an arithmetic operation, the expression with a lower rank data type will be converted automatically to higher rank data type. Usually, a data type with a larger storage size has a higher rank. A conversion from a lower rank data type to a higher rank data type is considered safe, because there is no data lose during the conversion.

The tutorial exercise shows you some good examples on automatic conversion during arithmetic operations:

-- INT / INT, No conversion
-- Returning INT
DECLARE @i INT;
SET @i = 1;
SELECT @i/3;
GO
0

-- NUMERIC / INT, INT converted to NUMERIC
-- Returning NUMERIC
DECLARE @d NUMERIC(9,6);
SET @d = 1.0;
SELECT @d/3;
GO
0.33333333

-- INT * NUMERIC, INT converted to NUMERIC
-- Returning NUMERIC
DECLARE @pi NUMERIC(15,7);
DECLARE @d INT;
SET @pi = 3.1415927;
SET @d = 10000000;
SELECT @d*@pi;
GO
31415927.0000000

-- INT * FLOAT, INT converted to FLOAT
-- Returning FLOAT
DECLARE @pi FLOAT(24);
DECLARE @d INT;
SET @pi = 3.1415927E+00;
SET @d = 10000000;
SELECT @d*@pi; -- @d converted from INT to FLOAT
GO
3.141593E+07

 

⇒Numeric Expressions and Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials