Question

In "Building a Scalable Data Warehouse with Data Vault 2.0" on page 358, I find these two statements:
1) 11.2.3.2 Storage Requirements - "When storing hashes, avoid using the varchar datatype."
2) 11.2.3.3 - Tool and Platform Compatibility - "For that reason, the recommended datatype to store hash keys is varchar..."

My head hurts :)
I understand the reasoning behind both statements.  What IS the recommended best practice?

Thanks.

Answer 1

Dear Joel,

the second statement is a typo - use the char(32) data type for storing MD5 hash values. I'm going to add this to the errata page (upcoming) of the book.

Reason for the use of char(32) instead of varchar(32) is that the hash key has always a fix length and in many cases, relational database systems add fixed length columns to the main page of the table and don't add them to some shadow page (which would require additional page retrievals for join operations).

Hope that helps,

Mike