Generating Unique Keys in Fabric Data Warehouse

[harry_allen]

Hi All,

This is my first time posting in a forum. Please apologize me in case if I make some mistakes.

We have a huge dataset, and we are planning to generate unique identifiers in a warehouse table. We tried following Microsoft recommended approach by creating a rowid column with bigint data type  and filling the column with values CONVERT(BIGINT, CONVERT(VARBINARY, CONCAT(NEWID(), GETDATE()))) AS [Row_ID] as refrenced in the article: Generate-Unique-Identifiers .

But when we follow this approach, we are getting many duplicate rowids that defats the purpose of generating unique identifiers. Has anyone faced this error before, if yes what fix did you use to resolve this error? If you have not faced this error, do you have some suggestions that can help acheive this objective? I am all ears and open to suggestions and healthy discussions. 

Appreciate your time and effort!!

[v-pagayam-msft]

Hi @harry_allen ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution and accept as solution. It would be greatly appreciated by others in the community who may have the same question.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @harry_allen ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your answer and Accept it as solution. It would be greatly appreciated by others in the community who may have the same question

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @harry_allen ,

I just wanted to kindly follow up to see if you had a chance to review the previous response provided by us. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[harry_allen]

Hi Pallavi,

Thanks for the reply. But GUID isnt supported in  Microsoft Fabric Data warehouse.

In this Microsoft's article Generate-Unique-Identifiers tells us to use NEWID() along with date to get unique values. But in our case even after using this combination we get duplicate values. 

Best Regards,

Hari

[v-pagayam-msft · Accepted solution]

Hi @harry_allen ,
Thank you for your follow-up.

Seems you are facing duplicate values when generating unique identifiers using NEWID() and GETDATE().
GETDATE() has limited precision (3 milliseconds), which can lead to duplicate values when multiple rows are inserted in quick succession.

Instead of GETDATE(), you may use SYSDATETIME() because it has higher precision (up to 7 decimal places for seconds), reducing the chances of duplicates.

Refer the link for detailed understanding.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @harry_allen ,
Thank you for choosing Microsoft Fabric Community Forum!

Upon reviewing the information provided, Although I have not encountered this error, the suggestions below might assist you in resolving the issue.

• It's preferable to store GUIDs in a uniqueidentifier column without conversion to avoid truncation and ensure each identifier remains unique.

• For sequential numbers, use the ROW_NUMBER() function with proper concurrency controls to generate sequential unique identifiers within the scope of a query. 

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Regards,
Pallavi.