I am debating which datatype to use when storing a SHA256 hash in SQL Server. Should it be CHAR(64) or BINARY(32) ... The column will be part of a unique clustered index. I know that I'm probably splitting hairs at this point, however I want to do this right the first time and I know that at times primitive data types are faster and other times the newer more exotic types perform better. ( yes I know char(64) isn't all that new, but it's newer than byte storage )
I've looked around and can't find anything about the performance of one vs. the other in terms of search, etc.
The choice of data type might depends on how you're going to use (update/consume) the data.
For our company's data warehouse, we choose to store the hashed value as BINARY(32), because this value is used as primary key or foreign key, and it never needs to be consumed as CHAR or VARCHAR. The benefit is less storage needed, so the data size of related index is smaller as well. Overall, it just leads to a better performance.
I guess CHAR(64) will be preferrable, if you need to interact with applications that will not handle binary values, such as logging application or command-line interface (CLI) applications.
DECLARE @binTest BINARY(32)
DECLARE @varTest1 VARCHAR(66)
DECLARE @varTest2 VARCHAR(64)
SELECT @binTest = HASHBYTES('SHA2_256', 'test')
SET @varTest1 = CONVERT(VARCHAR(66), @binTest, 1)
SET @varTest2 = CONVERT(VARCHAR(66), @binTest, 2)
PRINT @binTest
PRINT @varTest1
PRINT @varTest2