Forum Discussion

Lee Taylor's avatar
Lee Taylor
Copper Contributor
Apr 05, 2021

HashBytes Conversion

I have run into a problem with PowerBI which does not support binary data. We are currenly using the Data Vault model where the Keys are stored as binary(64) generated from HasBytes('SHA2_512', ...). PowerBi will not recognize this and allow the joining of table realtionships. Can this 64 bye binary data be converted reliably to a data type such as bigint such as

 

Select Cast(Binary64Value as BigInt)

 

1 Reply

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Apr 06, 2021

    BigInt has a storage size of 8 byte; 64 byte binary never fits in. You can try it with varchar instead. Keep in mind, a hash is no guarantee for unique values; it's just a hash.

     

    select convert(varchar(64), HashBytes('SHA2_512', 'Test Test Test'))

     

Resources