Auto increment considering Tenant id

Question

I'm using SQL Server and need to keep an accounting number with reference to the tenant id.

I cannot use the auto increment id because it is sequentially increments and has gaps in the client applications.

I couldn't use computed columns as aggregate functions like Max is not allowed

What would be the best approach for this?

Answer 1

You also can process when insert data, For example:

insert into table1(ID,TenantId,PaymentId)
select 6,2,isnull(max(PaymentId)+1,1)
from table1 where TenantId=2 
group by TenantId

If you want to use trigger,This is a sample, In ths sample, even you specify a PaymentId when inserting data, this trigger also recalculating the PaymentId

    DROP TABLE table1
    CREATE TABLE Table1(ID INT IDENTITY(1,1),TenantId INT ,PaymentId INT)

    CREATE TRIGGER trg_UpdatePaymentId 
       ON  dbo.TABLE1
       AFTER INSERT
    AS 
    BEGIN

        SET NOCOUNT ON;

        UPDATE t SET t.PaymentId=a.rn
        FROM dbo.TABLE1 AS t INNER JOIN (
            SELECT i.ID,(ISNULL(c.MaxPaymentId,0)+ ROW_NUMBER()OVER(PARTITION BY TenantId ORDER BY ID)) AS rn FROM Inserted AS i
            OUTER APPLY(
                SELECT MAX(tt.PaymentId) AS MaxPaymentId FROM Table1 AS tt WHERE tt.TenantId=i.TenantId AND NOT EXISTS(SELECT 0 FROM Inserted AS ii WHERE ii.ID=tt.ID)
            ) AS c
        ) AS a ON a.ID=t.ID 


    END
    GO


    INSERT INTO table1(TenantId)VALUES(1),(2),(1),(1)
    SELECT * FROM dbo.TABLE1

ID          TenantId    PaymentId
----------- ----------- -----------
1           1           1
2           2           1
3           1           2
4           1           3