-5

Why SELECT 1/5 result is 0 instead of 0.2 in SQL server Transact SQL?

IF i use SELECT 1.0/5 result is 0.200000

ontananza
  • 362
  • 6
  • 7
  • 2
    Why would it result in anything else...? If I have 2 `varchar` values and concatenate them, should I get an `nvarchar`? No. If you have 2 `int` data types and do *something* with them, you get an `int` back. – Thom A Oct 14 '21 at 17:07
  • 1
    It is documented to work that way. What more could you want? – Andrew Morton Oct 14 '21 at 17:08
  • 1
    A `float`, however, would be an *awful* choice of a data type to get back as well if you were doing division with `int` values. You'd be inputting base 10 values and getting back a base 2; that would result in all sorts of rounding issues. At worst you could "expect" a `decimal`/`numeric` but that's still wrong; an application shouldn't be implicitly converting data types just because you're doing a specific type of arithmetic. – Thom A Oct 14 '21 at 17:10
  • This behavior is actually the standard for all languages I know of. – PMF Oct 14 '21 at 17:16
  • 1
    Of further related interest https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values-using-t-sql and https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15 – squillman Oct 14 '21 at 17:24
  • Does this answer your question? [SQL Server, division returns zero](https://stackoverflow.com/questions/1666407/sql-server-division-returns-zero) – Charlieface Oct 14 '21 at 18:32

1 Answers1

1

I'm not sure why you would expect anything else here. If you have 2 values of the same data type and do something (that isn't explicitly or implicitly casting the data type) with them why would you expect to get a different data type back? This is standard behaviour across every language, not just T-SQL or even SQL as a whole.

As I mentioned in the comment, if you had 2 varchar values and concatenated them, you wouldn't expect to get an nvarchar or perhaps a char back; you'd expect to get a varchar back. If you subtracted a datetime from a datetime (which as much as I hate, you can do) you wouldn't expect a time or perhaps a int value back.

Just because you're doing division doesn't change that; by providing int values you are, by definition, stating you want a int value back.

For the latter statement, SELECT 1.0/5, you have 2 different data types, a decimal(2,1) and an int respectively. When dealing with data of differing types one of the data types has to be implicitly converted to the data type of the other; which is determined by Data Type Precedence. decimal has a higher precedence that int, so that int is converted first, and then the division completed.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thank you for your answer, in many programming languages the aritmetic processor cast 2 integer division as float or real type that why i didnt catch this glinch of TSQL, afterwards is SQL. – ontananza Oct 14 '21 at 17:45
  • 1
    It's not a glitch at all. If you are using a language that does that, ***THAT*** is the glitched language, @ontananza . – Thom A Oct 14 '21 at 17:48