Help! Is it possible to use Row_Number with Concurrent Inserts?

Hi arpost 
#1
I presume you are working on a Warehouse. Given that delta tables support transactions (Transactions in Warehouse tables - Microsoft Fabric | Microsoft Learn), you can use a (control) table just to persist the max key, using the strategy of your preferences.

Just a simple example: Lets suppose you have a table ControlTable with a column id. The following sp will update the id and return the new value atomically, therefore all the processes must invoke this sp to get their unique id.

CREATE PROC [dbo].[GetMaxId]
    @currentID INT OUTPUT
AS
BEGIN
    -- Start a new transaction
    BEGIN TRANSACTION;

    -- Set the transaction isolation level to SERIALIZABLE
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    -- Get the current ID
    SELECT @currentID = id FROM dbo.ControlTable;

    -- Increment the ID
    SET @currentID = @currentID + 1;

    -- Update the table
    UPDATE dbo.ControlTable SET id = @currentID;

    -- Commit the transaction
    COMMIT TRANSACTION;
END

Another option would be using GUID instead of a number if the data type is not strictly necessary. You can get a unique identifier by running SELECT NEWID();

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