CREATE TABLE dbo.MyTable ( myKey INT IDENTITY PRIMARY KEY, category INT, incrementalId INT);GOcreate table dbo.nextCategoryID ( category int, nextidvalue int, constraint PK_nextCategoryID primary key clustered( category, nextidvalue ));GOcreate trigger numberByCategory on dbo.MyTableafter insert as-- Automatically add any net new categoryinsert into dbo.nextCategoryID ( category, nextidvalue ) select distinct category, 1 as nextidvalue from inserted where not exists ( select * from dbo.nextCategoryID s where s.category = inserted.category );-- Number the new rows in each incoming categorywith numberedrows as ( select i.myKey, i.category, n.nextidvalue - 1 + row_number() over ( partition by i.category order by i.category ) as incrementalId from inserted i join dbo.nextCategoryID n on i.category = n.category)update m set incrementalId = n.incrementalIdfrom dbo.MyTable mjoin inserted i on m.myKey = i.myKeyjoin numberedrows n on n.myKey = i.myKey;update dbo.nextCategoryID set nextidvalue = 1 + ( select max( m.incrementalId ) from inserted i join dbo.MyTable m on i.myKey = m.myKey where i.category = nextCategoryID.category )where exists ( select * from inserted i where i.category = nextCategoryID.category);GO-- Test dataINSERT INTO dbo.MyTable (category) VALUES (100);INSERT INTO dbo.MyTable (category) VALUES (200);INSERT INTO dbo.MyTable (category) VALUES (100);INSERT INTO dbo.MyTable (category) VALUES (100);INSERT INTO dbo.MyTable (category) VALUES (100);INSERT INTO dbo.MyTable (category) VALUES (200);insert into dbo.MyTable (category) values ( 200 ), ( 200 ), ( 100 ), ( 300 ), ( 400 ), ( 400 )SELECt *FROM dbo.MyTable;
