How can I generate sequentially increasing row numbers for a table within Fabric Datawarehouse?

Hi bexy 
Thanks for using Fabric Community.
We can use a work around technique in data warehouses to assign a unique identifier to each row of a table.
I have created the repro:

For more information please refer to this document:
Generate unique identifiers in a warehouse table in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

Hope this helps. Please let me know if you have any further questions.

bexy, one thing of which to be aware, though, is this only works if you aren't running multiple scripts that are inserting to the same table. If you have two scripts that insert into the same table around the same time, the workaround risks creating duplicate ID numbers since both scripts would be starting from the same MAX.

There's an idea for people to upvote for Microsoft (upvote link) to add an official identity feature to Fabric so we don't have to use the workaround. The good news is a Microsoft person replied and stated this is planned for 2024, though I haven't seen it on any release plans yet.