Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!
Solved! Go to 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!
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!
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!
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!
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
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!
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.
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.