How to get Decimal/Float Output from Integers Division in SQL Server

I am writing this post to answer once of the question that I posted while back for TSQL Interview Question list

"If I run Select 100/11 what output will I get?"


When you divide Integer by an Integer in SQL Server, SQL Server returns Integer output.

Fig 1: Divide Integer by Integer in SQL Server 

As we can see from our above example, SQL Server output is Integer for Integers division. If we want to get float or Decimal output, Either our denominator or Numerator should be float or decimal type. 
If we have both denominator or numerator as Integers, we can use convert or cast functions to convert one of them to float/decimal so we can get our results as float/decimal.

There are multiple techniques you can use to convert/cast to integer to float/decimal, here are some examples

 SELECT CAST(100 AS FLOAT) /11 AS OutputResultSELECT 100/CAST(11 AS FLOAT) AS OutputResultSELECT 100/CAST(11 AS DECIMAL(12,0)) AS OutputResultSELECT CAST(100 AS DECIMAL(12,0))/11 AS OutputResult


Fig 2: Get Decimal output result from Integer division in SQL Server

4 comments:

  1. this blog was really great, never seen a great blog like this before. i think im gonna share this to my friends.. Hex To Decimal

    ReplyDelete
  2. You provide useful post that highlighting an important aspect of SQL Server division. In the realm of Data Science, precision and accuracy are paramount. When performing calculations or analysis on numerical data, ensuring the correct data type and precision is crucial. SQL Server's default behavior of returning an integer output from integer division can lead to unintended consequences or inaccuracies when working with decimal or float values in a Data Science context. To know Data science career then visit to The future of Data Science education: Online Courses, Boot Camps, and Degrees.

    ReplyDelete