sql - How to get the next number in a sequence -

i have table this:

+----+-----------+------+-------+--+ | id | part      | seq  | model |  | +----+-----------+------+-------+--+ | 1  | head      | 0    | 3     |  | | 2  | neck      | 1    | 3     |  | | 3  | shoulders | 2    | 29    |  | | 4  | shoulders | 2    | 3     |  | | 5  | stomach   | 5    | 3     |  | +----+-----------+------+-------+--+ 

how can insert record next seq after stomach model 3. here new table suppose like:

+----+-----------+------+-------+--+ | id | part      | seq  | model |  | +----+-----------+------+-------+--+ | 1  | head      | 0    | 3     |  | | 2  | neck      | 1    | 3     |  | | 3  | shoulders | 2    | 29    |  | | 4  | shoulders | 2    | 3     |  | | 5  | stomach   | 5    | 3     |  | | 6  | groin     | 6    | 3     |  | +----+-----------+------+-------+--+ 

is there way craft insert query give next number after highest seq model 3 only. also, looking concurrency safe.

if not maintain counter table, there 2 options. within transaction, first select max(seq_id) 1 of following table hints:

1. with(tablockx, holdlock)
2. with(rowlock, xlock, holdlock)

tablockx + holdlock bit overkill. blocks regular select statements, can considered heavy though transaction small.

a rowlock, xlock, holdlock table hint better idea (but: read alternative counter table further on). advantage not block regular select statements, ie when select statements don't appear in serializable transaction, or when select statements don't provide same table hints. using rowlock, xlock, holdlock still block insert statements.

of course need sure no other parts of program select max(seq_id) without these table hints (or outside serializable transaction) , use value insert rows.

note depending on number of rows locked way, possible sql server escalate lock table lock. read more lock escalation here.

the insert procedure using with(rowlock, xlock, holdlock) follows:

declare @target_model int=3; declare @part varchar(128)='spine'; begin try     begin transaction;     declare @max_seq int=(select max(seq) dbo.table_seq with(rowlock,xlock,holdlock) model=@target_model);     if @max_seq null set @max_seq=0;     insert dbo.table_seq(part,seq,model)values(@part,@max_seq+1,@target_model);     commit transaction; end try begin catch     rollback transaction; end catch 

---

an alternative , better idea have counter table, , provide these table hints on counter table. table following:

create table dbo.counter_seq(model int primary key, seq_id int); 

you change insert procedure follows:

declare @target_model int=3; declare @part varchar(128)='spine'; begin try     begin transaction;     declare @new_seq int=(select seq dbo.counter_seq with(rowlock,xlock,holdlock) model=@target_model);     if @new_seq null          begin set @new_seq=1; insert dbo.counter_seq(model,seq)values(@target_model,@new_seq); end     else         begin set @new_seq+=1; update dbo.counter_seq set seq=@new_seq model=@target_model; end     insert dbo.table_seq(part,seq,model)values(@part,@new_seq,@target_model);     commit transaction; end try begin catch     rollback transaction; end catch 

the advantage fewer row locks used (ie 1 per model in dbo.counter_seq), , lock escalation cannot lock whole dbo.table_seq table blocking select statements.

you can test , see effects yourself, placing waitfor delay '00:01:00' after selecting sequence counter_seq, , fiddling table(s) in second ssms tab.

---

ps1: using row_number() on (partition model order id) not way. if rows deleted/added, or id's changed sequence change (consider invoice id's should never change). in terms of performance having determine row numbers of previous rows when retrieving single row bad idea.

ps2: never use outside resources provide locking, when sql server provides locking through isolation levels or fine-grained table hints.