Expand Ingest Instance incremental loading options

Hi,

there is a reason why int and date(time) can be used and numerics and floats cannot: depending on the scale and precision of fixed precision types and the specific implementations of the source systems there may be different results for the same comparison. The naive approach is to do something like abs(a - b) < precision to determine whether a == b for instance, but this runs into problems if what you are checking is more precise than the global precision.

Your incremental high-water mark (_I table for instance) will store the highest value, but your source could be an AS/400 or an Oracle database or a Sybase Advantage running on 32-bit architecture. All of these will implement comparisons differently and therefore require special handling, and in many cases not be able to do so efficiently whereas ints generally result in efficient indexes. You would also need to specify a specific scale and precision that matches with the expected values in the source systen translated to SQL Server decimal values to avoid unexpected results.

If you are dealing with floats this gets even worse, you may need to represent a value that cannot be represented by a float exactly but only a few ULPs away.

As an example let's generate the value 60 in two different ways:

SELECT 1.0 / (1.0 / 60.0)

and

SELECT 1.0E0 / (1.0E0 / 60.0E0)

The first uses numeric/decimal literals and the second uses float literals. Both should, in a perfect numerical representation, result in the value 60. As computers are binary beasts, this is not what happens. You would expect the results to be equal (==), so let us test:

SELECT CASE 
	WHEN (1.0 / (1.0 / 60.0)) > (1.0E0 / (1.0E0 / 60.0E0)) THEN 'What?!?'
	ELSE 'All could be well...'
END

You will see that the decimal result is larger than the floating point result. Note that, for added fun, SSMS rounds floats to make them more legible for you so use SQLCMD to see the raw results.

For more info: https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html