IDENTITY column for generation of Surrogate keys in Fabric Warehouse

[VirajD]

Hi,

 Since Fabric Warehouse does not support the IDENTITY functionality on a column for Surrogate key generation, is anyone aware of any alternate practical method to generate surrogate keys apart from the NEWID() function and MAXID + ROW_NUMBER() approach ?

[payyan_s]

Not making much sense to me. The identity column is giving protection and feasibility to the user to update any other column values without creating duplicates. When you use haskey, will it not change when the underlying data is changed and lead to potential duplicate values?

[v-prasare]

Hi @VirajD,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[VirajD]

The solution provided is accepted.

[DataBard · Accepted solution]

Hi @VirajD ,

One other approach I've seen used in Fabric is creating a hash of your data and using that as a surrogate key. 

For example if you have a PersonID, you could do this:

HASHBYTES('SHA2_256', CONVERT(varchar(800), PersonID))

Depending on your definition, it's not a surrogate key because it's derived from your existing data.

If this isn't a viable option, I haven't seen any alternatives to date beyond the ones you've ruled out. You'd likely need to either build your own solution to create a surrogate or otherwise find another option.