I have created a filed in each of my table and made it Primary Key.
My question is:
What happens if we Deoply and Execute on DSA table with a column that creats uniqe id with NEWID()? Does it create new IDs that are different, or does the table keep the IDs generated before?
Page 1 / 1
Hi,
do you need a uniqueidentifier or are you essentially making a surrogate key?
Uniqueidentifiers are not deterministic, so you will not get the same one twice for the same record by calling NEWID() . I.e. if you need to redeploy the table you will get new ids for the same records. If you want something deterministic, use a hash field or a supernatural key. It will give you the same varbinary or int for the same business key.
If you really want to use a uniqueidentifier type, then I would make sure the table is a type 1 history table and keep a mapping of business key → uniqueidentifier stored somewhere. Then you can lookup up the unique identifier to your table.
@rory.smith I thought about the issue during the weekend and i guessed that i might have to create a key store. So this is what i have done. Is it ok?
I am using it in both tables:
One thing i am not sure about (as i have mentioned in my post above) is that there are no unique columns. Since the creation of SNK requires atleast one busienss key (according to this video) i have used brukerReference, but this could be repetitive.
Supernatural keys are deterministic (same business key input results in the same bigint key). This does mean that if there are multiple instances of the same brukerReferanse in the table they will get the same Supernatural key value. If you also make that field the primary key, any duplicates will disappear from the table and be stored in the error tables (_L, _M) under normal quality handling settings.
If the tables represent a person and a company, you would need to find (or create synthetically) identifiers that make them unique. If the UUID is empty sometimes, I would expect to find something else like a name or address to use. If that doesn't exist, the data is likely too broken to use and that would be something to ask the datasource stakeholders about.
For your Supernatural key store settings, I would advise to use the more modern hashing algorithm labelled ‘Fastest’ as this one will slow down large tables.
