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

Hi @arpost 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

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.