Why does inserting a value into a temp table's BINARY(8) column cause an error when casting the same value to BINARY(8) does not?

Question

When I execute this:

DROP TABLE IF EXISTS #QueryHashes;
CREATE TABLE #QueryHashes (query_hash BINARY(8) NOT NULL PRIMARY KEY);
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the value is a legitimate BINARY(8)
/* Insert your query_hash values inside the outer parenthesis below, 
 each wrapped in parenthesis, comma-separated (for example, "(0x89E35F2D4C638298), (0x89E35F2D4C638298)") 
*/
INSERT INTO #QueryHashes (query_hash)
VALUES (0x0169857CCD5BBBE4E);
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the value is a legitimate BINARY(8)

I get this error:

0x00169857CCD5BBBE Msg 2628, Level 16, State 1, Line 9 String or binary data would be truncated in table 'tempdb.dbo.#QueryHashes________________________________________________________________________________________________________000000000D06', column 'query_hash'. Truncated value: ''. The statement has been terminated. 0x00169857CCD5BBBE

Note that the value in the error message seems to be an empty string.

Clearly, the insert is failing, while the casts work. Can anyone help me understand what is wrong with the insert or table definition? Even if I remove the "NOT NULL PRIMARY KEY", I still get the same error.

(The eventual intent is to insert a few values into the table, and join it into a big query.)

• mustaccio: Perhaps it's because your value is 9 bytes long? CAST will happily truncate the value that's too long.
• Akina: BINARY(8) == 16 hex digits whereas your values contains 17 digits..

Answer 1

As @mustaccio noted, it's because your value is 9 bytes long. CAST will truncate the value for you if it's too long. See below -

DROP TABLE IF EXISTS #QueryHashes;
CREATE TABLE #QueryHashes
(
 query_hash BINARY(8) NOT NULL PRIMARY KEY
);
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the value is a legitimate BINARY(8)
/* Insert your query_hash values inside the outer parenthesis below, 
 each wrapped in parenthesis, comma-separated (for example, "(0x89E35F2D4C638298), (0x89E35F2D4C638298)") 
*/
INSERT INTO #QueryHashes
(
 query_hash
)
VALUES
(CAST(0x0169857CCD5BBBE4E AS BINARY(8)));
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(9)); -- just testing that the value is a legitimate BINARY(9)
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the value is a legitimate BINARY(8)
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(7)); -- just testing that the value is a legitimate BINARY(7)

Produces this in the console -

0x00169857CCD5BBBE
(1 row affected)
0x00169857CCD5BBBE4E
0x00169857CCD5BBBE
0x00169857CCD5BB

• Mark Freeman: Very interesting. My actual source for the value is a Kusto query looking at Azure Automatic Tuning recommendations for my Azure SQL Databases that returns JSON with a QueryHashes[0] attribute. I just researched this and found that some values are BINARY(9) instead of the expected BINARY(8) because they have a leading zero in the hexadecimal representation. This is apparently a bug in AzureDiagnostics. So I need to add some extra code to handle those values. <sigh>
• Caleb Carl: It might be best to make an edit in the title of the question showing that this is a bug in the version of AzureDiagnostics and link any documentation you have. It's indeed a sad day when a bug lays us low.