Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
harry_allen
Regular Visitor

Generating Unique Keys in Fabric Data Warehouse

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!!

1 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.

vpagayammsft_0-1741160105399.png


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!


View solution in original post

6 REPLIES 6
v-pagayam-msft
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

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
Regular Visitor

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.

vpagayammsft_0-1741160105399.png


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
Community Support
Community Support

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.




 

 



 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

June FBC25 Carousel

Fabric Monthly Update - June 2025

Check out the June 2025 Fabric update to learn about new features.