Should a primary key which is a hash be stored as BINARY or CHAR (in hex)?

Question

Records are identified by 32 byte SHA-2 value. Using BINARY is more compact (8 bits per byte vs 4 bits per byte) but that is offset by the increased difficulty in using binary value in adhoc queries (troubleshooting, etc). I am leaning towards storing as hex strings in 64 byte CHAR field. Are there any other issues or concerns beyond the 4 byte 32 byte per record overhead?

Update: Based on comments I did some testing in SQL 2012 and there is no longer any 'difficulty' in adhoc queries (SSMS) involving binary keys. My experience on the topic was a little dated. In SSMS for SQL Server 2000 there was no implicit conversion from binary to hex strings. So it became necessary to explicitly convert between binary and hex strings by functions to get any meaningful results. It looks like that changed in SQL Server 2005.

Answer 1

I was looking for a similar answer to this question. However it was a question of storing a GUID as binary or varchar in a database. Different type of object but same principle.

Here is some more information: How should I store GUID in MySQL tables?

I also found a useful article that compared the performance of querying table with millions of rows with binary primary key and millions as varchar. The search performance was huge when using binary so I use this now when I know I'm going to be dealing with a lot of rows. Unfortunately I was unable to find the original article.

Once you get everything setup to manage the binary information it's no more work than using a varchar.