TSQL: Which real number type results in faster comparisons - 【StackMirror】|performance|tsql|floating-point|decimal|real-datatype

Among TSQL types (MSSQL): real, float and decimal; which type would result in faster comparisons?

Would decimal use hardaware FPU computation or is it purely in software?

2012-04-05 19:06
by loopedcode

In most cases, SQLServer instances are not very demanding on CPU; its bottlenecks are usually related to IO and network subsystems. Thus, this kind of micro-optimization should only be thought of if a real problem is detected on a running system - Gerardo Lima 2012-05-22 13:03

Decimals cannot use the FPU. You get best performance with float or real which map to a standard IEEE floating point number which is supported by the FPU.

float = double real = single

Of course, single is faster.

2012-05-22 12:47
by usr

Thanks, thats what I was guessing also - loopedcode 2012-06-07 03:10

Are these relative comparisons >, < or equality comparisons =, != ?

Floats and reals are approximation data types where as a decimal is an actual representation. If you're doing equality, you'll want to stay away from floats and reals. So that leaves decimals.

More than likely, SQL Server won't go to the FPU for relative comparisons. FPU and other coprocessors are used for arithmetic.

2012-04-05 19:25
by Michael Rice

Yes, mostly > or < comparisons - loopedcode 2012-04-05 20:45

Why wouldn't it use the FPU for comparisons? How else would it do the comparisons - usr 2012-05-22 12:47