Non fractional division (int/float)

For me a RDBMS is a high level tool. Something very opposite to using low level languages like C, C++ or... Assembler. From user/developer perspective it should exist outside of binary realm. You shouldn't be exposed to any binary considerations. Until you want to, of course.
It's obvious, that data has some binary representation and it may and could be beneficial if the user, and developer especially, is aware of it but it shouldn't be a requirement.
This binary representation is a crucial thing to know for administrators (maybe not on the row but a data file level). The database performance relies on it greatly. It's best if such representation is aligned with the hardware. Things like endianness, word lengths or architecture (RISC/CISC) imply different internal implementations. We can imagine that if datafiles are in line with such factors RDBMS will perform better as there will be no conversions necessary.True, but it's the administrator's task to make it work like that (if RDBMS allows for different datafile formats). Unfortunately (my point of view) for SQL Server users it always existed in Microsoft x86 based world and as such - these things never ever changed (apart from being expanded with new features).

It's very obvious that SQL Server developers decided to incorporate internal CPU database into server's datatypes. We can see types like int, bigint or float. For sure, it's probably quick and very effective, especially when it comes to storage but it also introduces some of pitfalls.

The most notable one is a no-reminder division when both numbers are integer:

select 2/3 as result

result
-----------
0

To make sure you get what you want (unless it IS what you want) you have to cast at least one of them to floating point:

select 2.0/3 as result;
select cast(2 as float)/3 as result;
select cast(2 as numeric)/3 as result;

result
---------------------------------------
0.666666

(1 row affected)

result
----------------------
0,666666666666667

(1 row affected)

result
---------------------------------------
0.666666

(1 row affected)


The other thing worth mentioning is a way the floating point data is represented binary. It's all explained nicely in the SQL Servers documentation so I won't do this again. Suffice to say the precision is highly influenced by the exponent so, let me quote documentation exactly: "Floating point data is approximate; therefore, not all values in the data type range can be represented exactly".

Fortunately SQL Server does support other numeric datatypes like numeric for example which I strongly encourage anyone to consider using, especially when working on finance data.

Just make sure you know what you're doing and it's generally a good idea to stay away of binary data unless that's exactly what you want to do. It's all right to use int/bigint for identifiers but is very questionable for me.


Best