I have a table with a column that should auto-increment, however it should increment over another descriminator column

for example:

So if I would enter another entry with Descriminator "A" I would get Filenumber 3. But by adding an entry with "B", i would get 2, because it's the 2nd "B" Row.

The only way I know is a Update-Trigger but is there an easier solution or if it's the only way, is it reliable with heavy traffic (load-balanced system with many users)?

CodePudding user response：

The only way I know is a Update-Trigger but is there an easier solution or if it's the only way, is it reliable with heavy traffic (load-balanced system with many users)?

There's not really any way to do this that doesn't sacrifice scalability.

UpdateTrigger With Try Catch in combination with an uniqueindex over these columns maybe?

Yes it's possible, but I wouldn't recommend it. You'll end up with blocking, and perhaps deadlocking so it will require careful coding, index design and testing. And best-case your DML will be much more expensive than it could be since it has to query all the other rows in the same discriminator, and there's simply no way for sessions to concurrently run DML within the same discriminator.

If you did want to go down this road, I would maintain a seperate table with Discriminator as the PK, and LastFileNumber. That way you don't have to scan over lots of rows to figure out the next FileNumber.

So something like:

create table Discriminator(Discriminator varchar(10) not null primary key, LastFileNumber int)

And then in your trigger or stored procedure for each row:

set nocount on
declare @discriminator varchar(10) = 'a';
declare @fn int
declare @fnt table(fn int)

merge discriminator with (updlock, holdlock)  as t 
using (select 1 fn) as s
on t.Discriminator = @discriminator
when matched then update set t.LastFileNumber = t.LastFileNumber   1
when not matched then insert (Discriminator,LastFileNumber) values (@discriminator,1)
output inserted.LastFileNumber
into @fnt;

set @fn = (select fn from @fnt)

But the easiest and best performing solution is to abandon the "requirement" that the FileNumbers are sequential within each "discriminator" and simply use a SEQUENCE or IDENTITY to generate them. so you get

and if you want to display the ordinal within each discriminator you can query it like

select
    Id,
    row_number() over (partition by Discriminator order by FileNumber) FileOrdinal,
    Discriminator
from T

CodePudding user response：

@Larnu has already alluded to this in the comments, but I thought it was worth showing you how simple this kind of thing can be, and with the right indexes calculating the descriminator should be happily swift enough!

In short, your table design can be changed to have a compound, clustered primary key on the file_number and an additional, incrementing field such as a revision_date.

CREATE TABLE your_table (
   file_number   int      NOT NULL
 , revision_date datetime NOT NULL CONSTRAINT df_your_table_revision_date (getutcdate())
 , CONSTRAINT pk_your_table PRIMARY KEY (file_number, revision_date)
);

You can then create a view on top of your table, which can be used for all your data access needs for simplicity:

CREATE VIEW your_data_access_view
  AS
SELECT filenumber
     , revision_date
     , Row_Number() OVER (PARTITION BY filenumber ORDER BY revision_date) AS descriminator
FROM   your_table
;

To show it in action:

INSERT INTO your_table (file_number) VALUES (1);
WAITFOR DELAY '00:00:01';
INSERT INTO your_table (file_number) VALUES (2);
WAITFOR DELAY '00:00:01';
INSERT INTO your_table (file_number) VALUES (1);

SELECT filenumber
     , revision_date
     , descriminator
FROM   your_data_access_view
ORDER
    BY filenumber
     , descriminator
;