How to create a unique ID for a Datawarehouse table

[kkoba]

Hello,

I am trying to create a unique column in a Fabric Datawarehouse table.

When I checked Microsoft Learn, it said that I can create unique data with the following statement.
https://learn.microsoft.com/ja-jp/fabric/data-warehouse/generate-unique-identifiers

SELECT
CONVERT(BIGINT, CONVERT(VARBINARY, CONCAT(NEWID(), GETDATE()))) AS [Row_ID],
[src].[O_OrderKey],
[src].[O_CustomerKey],
[src].[O_OrderStatus],
[src].[O_TotalPrice],
[src].[O_OrderDate],
[src].[O_OrderPriority],
[src].[O_Clerk],
[src].[O_ShipPriority],
[src].[O_Comment]
FROM [dbo].[Orders] AS [src];

When I checked here, I found that inserting tens of thousands of rows of data caused duplications in the Row_ID column.

I was able to avoid this by modifying it as follows, but what would be the best practice?

SELECT
ABS(CONVERT(BIGINT, HASHBYTES(''SHA1'', CAST(CONCAT(NEWID(), SYSDATETIME()) AS NVARCHAR(36))))) , ....

Thank you in advance.

[v-bmanikante]

Hi @kkoba ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @kkoba ,

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

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @kkoba ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante · Accepted solution]

Hi @kkoba ,

Thank you for reaching out to Microsoft Fabric Community Forum.

While the CONVERT(BIGINT, CONVERT(VARBINARY, CONCAT(NEWID(), GETDATE()))) approach can work for small inserts, it isn't fully reliable when inserting tens of thousands of rows, it tends to create duplicates because the conversion to BIGINT reduces the uniqueness of the original GUID + timestamp combo.

Your updated solution using:

ABS(CONVERT(BIGINT, HASHBYTES('SHA1', CAST(CONCAT(NEWID(), SYSDATETIME()) AS NVARCHAR(100)))))

is a much more robust workaround. Using a SHA1 hash maintains better uniqueness, especially for larger inserts or parallel operations, and it's great that you're also using SYSDATETIME() to add higher precision.

For best practices:

• This hash-based method is currently one of the most reliable ways to simulate a unique row identifier in Fabric DW.
• Just make sure that the hash result is handled appropriately and that it doesn't accidentally produce negative values (you're already using ABS() which is good).
• Also, be cautious with case sensitivity in Fabric DW, it can affect column names or string comparisons if you're not explicitly managing that.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy