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?
