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?