How can I verify that all possible FLOAT(53) values convert to unique BINARY(8) values?

Question

Part of our application code converts data types to BINARY for the purposes of hashing. For example, we convert BIGINT values to BINARY(8). The documentation warns that conversions may change between versions of SQL server:

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server.

As a defense against that, whenever we start supporting a new version of SQL Server we try to run tests to validate that all of our conversions are still valid. For something like BIGINT, we check the length of the converted value for the minimum and maximum values allowed for BIGINT. That hopefully means that we would know if, for example, SQL Server 2019 started to require BINARY(9) to fit all possible values of BIGINT.

How can we do this type of analysis for FLOAT(53)? Right now we think that all possible values map to unique values that fit in a BINARY(8), but I don't know how to validate that. This may not be as simple as just checking the number of bytes required for the data type. For example, the TIME data type requires 5 bytes for storage but must be converted to a BINARY(6) to avoid errors. Perhaps this is irrelevant, but it also makes me nervous that BINARY cannot be converted back to FLOAT. I admit that I might be overthinking the problem, so I welcome frame challenges as answers.

How can I write code to validate that all possible inputs for FLOAT(53) do not overflow a BINARY(8) and that two different FLOAT(53) values do not convert to the same BINARY(8) value?

Answer 1

The size, in bytes, of both BIGINT and FLOAT(53) are documented. It would be a breaking change to make either one not convertible back and forth to BINARY(8).

The documentation you quote is about the details of the conversion, which are undocumented and subject to change. EG the byte layout of a float(53) in this conversion could change. So if you convert a float to binary(8), store the binary(8) in a table, upgrade SQL Server, and convert the binary(8) back to float, you might not get the same value.

These particular types have well-known byte layouts. BIGINT is an 8-byte little endian integer, and float(53) is a standard double-precision floating point number. And it would be pretty surprising if one of those changed. But other types, especially CLR-based types, really might change their layout between versions.