How to create a unique ID for a Datawarehouse table

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