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.)

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