settingsLogin | Registersettings
Scalefree
Show Menu

Is Varchar recommended to hold hash keys?

+1 vote
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.
asked Jan 3, 2016 in Implementation by joelwittenmyer (170 points)

1 Answer

+2 votes
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
answered Jan 3, 2016 by molschimke (1,890 points)
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree
    DataVault.guru is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license.

    DataVault.guru | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke
    ...