sql auto increment based on ID

Question

Im using SQLServer 2017, I have a table that I want to auto increment for each id. Example Table A has columns

PolicyID, ClaimID, TranId

with the following values

ABC123, 111, 1

when another row gets inserted/added TranId will show 2 and so on but if the PolicyID is different lets say ABC456 then the expected TranId should be 1 but my table just keeps incrementing instead of per PolicyID.

Answer 1

You are trying to create a sequence and this shouldn't be stored in the table.

Try creating a view:

create vw_xxx as
(
select PolicyID, ClaimID
      , TranId = row_number() over (partition by PolicyID order by ClaimID)
from tableXXX
)

This is an example of how to do this. You need to partition and order by properly to get the right sequence.

If this table is large then you want to have an index on the partition,ordered by columns.