How to handle generate custom composite key from multiple request

Question

I would like to generate costume unique invoiceno with help of two column Year and CompanyId in SQL Server (composite key (Year, CompanyId, Invoiceno)).

I can do with custom login to generate this unique key but how can handle if multiple request come at same to insert record.

My expectation records as below :

InvoiceId | Year            | CompanyId | InvoiceNo
1         | 2018            |      1    | IN0001
2         | 2018            |      1    | IN0002
3         | 2018            |      2    | IN0001
3         | 2018            |      3    | IN0001
4         | 2019            |      1    | IN0001
5         | 2019            |      1    | IN0002
6         | 2019            |      2    | IN0001

Thanks in advance !

Answer 1

One can use ROW_NUMBER to get a number that can be used to form such InvoiceNo.

SELECT 
 InvoiceId, [Year], CompanyId, 
 'IN'+ RIGHT(
        CONCAT(
         REPLICATE('0',3), 
          ROW_NUMBER() OVER (PARTITION BY [Year], CompanyId ORDER BY InvoiceId)
        ) ,4) AS InvoiceNo
FROM YourInvoiceTable
ORDER BY InvoiceId

Test on db<>fiddle here