Decimal values in SQL for dividing results

Just another approach:

SELECT col1 * 1.0 / col2 FROM tbl1

Multiplying by 1.0 turns an integer into a float numeric(13,1) and so works like a typecast, but most probably it is slower than that.

A slightly shorter variation suggested by Aleksandr Fedorenko in a comment:

SELECT col1 * 1. / col2 FROM tbl1

The effect would be basically the same. The only difference is that the multiplication result in this case would be numeric(12,0).

Principal advantage: less wordy than other approaches.

SELECT CAST (col1 as float) / col2 FROM tbl1

One cast should work. ("Less is more.")

From Books Online:

Returns the data type of the argument with the higher precedence. For more information about data type precedence, see Data Type Precedence (Transact-SQL).

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated

You will need to cast or convert the values to decimal before division. Take a look at this http://msdn.microsoft.com/en-us/library/aa226054.aspx

For example

DECLARE @num1 int = 3 DECLARE @num2 int = 2

SELECT @num1/@num2

SELECT @num1/CONVERT(decimal(4,2), @num2)

The first SELECT will result in what you're seeing while the second SELECT will have the correct answer 1.500000