A Retained Key is an integer used to represent a particular business key in a database table. It is useful in the following scenarios:
- Optimising for speed of joins
- Enabling business keys with null values
- Handling composite keys with a large number of columns
- Integrating with systems which require a short, numeric identifier
A Retained Key will “retain” its value even if the record (non PK column) is changed. It can be likened to a hash key - which is a checksum, with separator, of the key values. If the source record is deleted, the retained key may also be deleted, or it can be “closed out” with a delete flag or an SCD 2 high date.
Hash keys have the advantage that they can be generated without joining back to the base table - so, faster load times. The disadvantage of hash keys is that they take more space - 32 bytes minimum, typically, vs 8 bytes for a numeric.
A Retained Key differs from a Surrogate Key in that a surrogate key is not linked to the business key of the table - somewhat like a UUID (Universally Unique IDentifier).
SAS ETL Developers will be familiar with the capabilities of DI Studio for managing retained keys
The SASjs/core library has a macro to assist SAS Programmers and Application Developers with the same. Features include:
- Table Lock Management (mp_lockanytable)
- Metadata Storage (max retained key)
- Uniqueness check (optional)
The documentation for the macro is here: https://core.sasjs.io/mp__retainedkey_8sas.html
This is the same macro used for updating tables with Retained Keys in Data Controller for SAS®. If you would like to empower your users to update mapping tables in your DDS or SAS-Hosted Data Store, with full quality control and audit trail, via a secure web interface - contact the SAS Apps team!