Algorithm to generate unique key across differnet env

Database

Algorithm to generate unique key across differnet env

I have  a requirement  to generate a surrogate key by passing few columns as input. Also,the surrogate key need to be same if I ran multiple times and also in different env (like dev/sit/prod). Wondering if  we can use hashing alogrthm to achieve this? 

 

Example: Col A= x; Col B = y;Col C = z;

If i pass column A,B,C as input,I should get 123 as surrogate key whenever i ran and aslo If if I ran in diff envs.     

 

 


Accepted Solutions
Supporter

Re: Algorithm to generate unique key across differnet env

I would go for a hash - sha256 for example - all environments will generate the same ids. You can hash within the DB or also on ETL server - just as you need.

If you want different ids for same inputs on different environments (something which is sometimes a good idea), you can use different salts per env.

Given a long enough hash hash synonyms are not an issue - space might be...

 

 

1 ACCEPTED SOLUTION
3 REPLIES
Supporter

Re: Algorithm to generate unique key across differnet env

I would go for a hash - sha256 for example - all environments will generate the same ids. You can hash within the DB or also on ETL server - just as you need.

If you want different ids for same inputs on different environments (something which is sometimes a good idea), you can use different salts per env.

Given a long enough hash hash synonyms are not an issue - space might be...

 

 

Re: Algorithm to generate unique key across differnet env

Thank you!. Finally we ended up using hash_md5 just to limit to 32 in length. Do you see any issue in using hash_md5?

 

Supporter

Re: Algorithm to generate unique key across differnet env

md5 has a long enough bit length. It is not considered as secure for crypthographic usage but for your purpose it should be still sufficient.