How to get the next number in a sequence

How to get the next number in a sequence

I have a table like 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 I insert another record with the next seq after Stomach for Model 3. So here is what the new table suppose to look 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 a way to craft an insert query that will give the next number after the highest seq for Model 3 only. Also, looking for something that is concurrency safe.

Answer by Gordon Linoff for How to get the next number in a sequence

The correct way to handle such insertions is to use an identity column or, if you prefer, a sequence and a default value for the column.

However, you have a NULL value for the seq column, which does not seem correct.

The problem with a query such as:

Insert into yourtable(id, Part, Seq, Model)      Select 6, 'Groin', max(Seq) + 1, 3       From yourtable;  

is that two such queries, running at the same time, could produce the same value. The recommendation is to declare seq as a unique, identity column and let the database do all the work.

Answer by Holmes IV for How to get the next number in a sequence

Since you want the sequence to be based on the a specific model, just add that into the where clause when doing the select. This will ensure the Max(SEQ) pertains only to that model series. Also since the SEQ can be null wrap it in a ISNULL, so if it is null it will be 0, so 0 + 1, will set the next to 1. The basic way to do this is :

Insert into yourtable(id, Part, Seq, Model)      Select 6, 'Groin', ISNULL(max(Seq),0) + 1, 3       From yourtable      where MODEL = 3;  

Answer by Charl for How to get the next number in a sequence

Let's first list the challenges:

1. We cannot use a normal constraint as there are existing null values and we also need to cater for duplicates as well as gaps - if we look at the existing data. This is fine, we will figure it out ;-> in step 3

2. We require safety for concurrent operations (thus some form or mix of transactions, isolation levels and possibly a "kinda SQL mutex".) Gut feel here is a stored proc for a couple of reasons:

   2.1 It protects more easily from sql injection

   2.2 We can control the isolation levels (table locking) more easily and recover from some issues which come with this kind of requirement

   2.3 We can use application level db locks to control the concurrency

3. We must store or find the next value on every insert. The word concurrency tells us already that there will be contention and probably high throughput (else please stick to single threads). So we must already be thinking: do not read from the same table you want to write to in an already complicated world.

So with that short prequel, let's attempt a solution:

As a start, we are creating your original table and then also a table to hold the sequence (BodyPartsCounter) which we are setting to the last used sequence + 1:

    CREATE TABLE BodyParts          ([id] int identity, [Part] varchar(9), [Seq] varchar(4), [Model] int)      ;        INSERT INTO BodyParts          ([Part], [Seq], [Model])      VALUES          ('Head', NULL, 3),          ('Neck', '1', 3),          ('Shoulders', '2', 29),          ('Shoulders', '2', 3),          ('Stomach', '5', 3)      ;        CREATE TABLE BodyPartsCounter          ([id] int          , [counter] int)      ;        INSERT INTO BodyPartsCounter          ([id], [counter])      SELECT 1, MAX(id) + 1 AS id FROM BodyParts      ;  

Then we need to create the stored procedure which will do the magic. In short, it acts as a mutex, basically guaranteeing you concurrency (if you do not do inserts or updates into the same tables elsewhere). It then get's the next seq, updates it and inserts the new row. After this has all happened it will commit the transaction and release the stored proc for the next waiting calling thread.

SET ANSI_NULLS ON  GO  SET QUOTED_IDENTIFIER ON  GO  -- =============================================  -- Author:      Charlla  -- Create date: 2016-02-15  -- Description: Inserts a new row in a concurrently safe way  -- =============================================  CREATE PROCEDURE InsertNewBodyPart   @bodypart varchar(50),   @Model int = 3  AS  BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;        BEGIN TRANSACTION;        -- Get an application lock in your threaded calls      -- Note: this is blocking for the duration of the transaction      DECLARE @lockResult int;      EXEC @lockResult = sp_getapplock @Resource = 'BodyPartMutex',                      @LockMode = 'Exclusive';      IF @lockResult = -3 --deadlock victim      BEGIN          ROLLBACK TRANSACTION;      END      ELSE      BEGIN          DECLARE @newId int;          --Get the next sequence and update - part of the transaction, so if the insert fails this will roll back          SELECT @newId = [counter] FROM BodyPartsCounter WHERE [id] = 1;          UPDATE BodyPartsCounter SET [counter] = @newId + 1 WHERE id = 1;            -- INSERT THE NEW ROW          INSERT INTO dbo.BodyParts(              Part              , Seq              , Model              )              VALUES(                  @bodypart                  , @newId                  , @Model              )          -- END INSERT THE NEW ROW          EXEC @lockResult = sp_releaseapplock @Resource = 'BodyPartMutex';          COMMIT TRANSACTION;      END;    END  GO  

Now run the test with this:

EXEC    @return_value = [dbo].[InsertNewBodyPart]      @bodypart = N'Stomach',      @Model = 4    SELECT  'Return Value' = @return_value    SELECT * FROM BodyParts;  SELECT * FROM BodyPartsCounter  

This all works - but be careful - there's a lot to consider with any kind of multithreaded app.

Hope this helps!

Answer by TT. for How to get the next number in a sequence

If you do not maintain a counter table, there are two options. Within a transaction, first select the MAX(seq_id) with one of the following table hints:

1. WITH(TABLOCKX, HOLDLOCK)
2. WITH(ROWLOCK, XLOCK, HOLDLOCK)

TABLOCKX + HOLDLOCK is a bit overkill. It blocks regular select statements, which can be considered heavy even though the transaction is small.

A ROWLOCK, XLOCK, HOLDLOCK table hint is probably a better idea (but: read the alternative with a counter table further on). The advantage is that it does not block regular select statements, ie when the select statements don't appear in a SERIALIZABLE transaction, or when the select statements don't provide the same table hints. Using ROWLOCK, XLOCK, HOLDLOCK will still block insert statements.

Of course you need to be sure that no other parts of your program select the MAX(seq_id) without these table hints (or outside a SERIALIZABLE transaction) and then use this value to insert rows.

Note that depending on the number of rows that are locked this way, it is possible that SQL Server will escalate the lock to a table lock. Read more about lock escalation here.

The insert procedure using WITH(ROWLOCK, XLOCK, HOLDLOCK) would look as follows:

DECLARE @target_model INT=3;  DECLARE @part VARCHAR(128)='Spine';  BEGIN TRY      BEGIN TRANSACTION;      DECLARE @max_seq INT=(SELECT MAX(seq) FROM dbo.table_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE model=@target_model);      IF @max_seq IS NULL SET @max_seq=0;      INSERT INTO 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 and probably a better idea is to have a counter table, and provide these table hints on the counter table. This table would look like the following:

CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq_id INT);  

You would then change the insert procedure as follows:

DECLARE @target_model INT=3;  DECLARE @part VARCHAR(128)='Spine';  BEGIN TRY      BEGIN TRANSACTION;      DECLARE @new_seq INT=(SELECT seq FROM dbo.counter_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE model=@target_model);      IF @new_seq IS NULL           BEGIN SET @new_seq=1; INSERT INTO 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 WHERE model=@target_model; END      INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@new_seq,@target_model);      COMMIT TRANSACTION;  END TRY  BEGIN CATCH      ROLLBACK TRANSACTION;  END CATCH  

The advantage is that fewer row locks are used (ie one per model in dbo.counter_seq), and lock escalation cannot lock the whole dbo.table_seq table thus blocking select statements.

You can test all this and see the effects yourself, by placing a WAITFOR DELAY '00:01:00' after selecting the sequence from counter_seq, and fiddling with the table(s) in a second SSMS tab.

---

PS1: Using ROW_NUMBER() OVER (PARTITION BY model ORDER BY ID) is not a good way. If rows are deleted/added, or ID's changed the sequence would change (consider invoice id's that should never change). Also in terms of performance having to determine the row numbers of all previous rows when retrieving a single row is a bad idea.

PS2: I would never use outside resources to provide locking, when SQL Server already provides locking through isolation levels or fine-grained table hints.

Answer by Vladimir Baranov for How to get the next number in a sequence

I would not try to store the Seq value in the table in the first place.

As you said in the comments, your ID is IDENTITY, which increases automatically in a very efficient and concurrent-safe way by the server. Use it for determining the order in which rows were inserted and the order in which the Seq values should be generated.

Then use ROW_NUMBER to generate values of Seq partitioned by Model (the sequence restarts from 1 for each value of Model) as needed in the query.

SELECT      ID      ,Part      ,Model      ,ROW_NUMBER() OVER(PARTITION BY Model ORDER BY ID) AS Seq  FROM YourTable  

Answer by Sunil for How to get the next number in a sequence

insert into tableA (id,part,seq,model)  values  (6,'Groin',(select MAX(seq)+1 from tableA where model=3),3)  

Answer by saik for How to get the next number in a sequence

create function dbo.fncalnxt(@model int)  returns int   begin  declare @seq int  select @seq= case when @model=3 then max(id) --else  end from tblBodyParts  return @seq+1  end  --query idea To insert values, ideal if using SP to insert  insert into tblBodyParts values('groin',dbo.fncalnxt(@model),@model)  

You can try this i guess. A novice shot, correct me if im wrong. i'd suggest using function to get the value in seq column based on model; you'll have to check the else case though to return another value you want, when model!=3, it'll return null now.

Answer by Jess Lpez for How to get the next number in a sequence

I believe the best bet to handle this kind of sequence generation scenario is the counter table as TT suggested. I just wanted to show you here a slightly simplified version of TT implementation.

Tables:

CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq INT);  CREATE TABLE dbo.table_seq(part varchar(128), seq int, model int);  

Simpler version (No SELECT statement to retrieve the current seq):

DECLARE @target_model INT=3;  DECLARE @part VARCHAR(128)='Otra MAS';    BEGIN TRY      BEGIN TRANSACTION;      DECLARE @seq int = 1      UPDATE dbo.counter_seq WITH(ROWLOCK,HOLDLOCK) SET @seq = seq = seq + 1 WHERE model=@target_model;      IF @@ROWCOUNT = 0 INSERT INTO dbo.counter_seq VALUES (@target_model, 1);      INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@seq,@target_model);      COMMIT  END TRY  BEGIN CATCH      ROLLBACK TRANSACTION;  END CATCH  

Answer by Mita for How to get the next number in a sequence

Assuming you have following table:

CREATE TABLE tab (      id int IDENTITY(1,1) PRIMARY KEY,      Part VARCHAR(32) not null,      Seq int not null,      Model int not null  );    INSERT INTO      tab(Part,Seq,Model)  VALUES      ('Head', 0, 3),      ('Neck', 1, 3),      ('Shoulders', 2, 29),      ('Shoulders', 2, 3),      ('Stomach', 5, 3);  

The query below will allow you to import multiple records, without ruine the model_seq

INSERT INTO      tab (model, part, model_seq)  SELECT      n.model,      n.part,      -- ensure new records will get receive the proper model_seq      IFNULL(max_seq + model_seq, model_seq) AS model_seq  FROM      (          SELECT              -- row number for each model new record              ROW_NUMBER() OVER(PARTITION BY model ORDER BY part) AS model_seq,              n.model,              n.part,              MAX(t.seq) AS max_seq          FROM              -- Table-values constructor allows you to prepare the              -- temporary data (with multi rows),              -- where you could join the existing one              -- to retrieve the max(model_seq) if any              (VALUES                  ('Stomach',3),                  ('Legs',3),                  ('Legs',29),                  ('Arms',1)              ) AS n(part, model)          LEFT JOIN              tab          ON              tab.model = n.model          GROUP BY              n.model n.part      ) AS t  

We need row_number() to ensure if we import more than one value the order will be kept. More info about ROW_NUMBER() OVER() (Transact-SQL)

Table-value constructor is used to create a table with the new values and join the MAX model_seq for model. You could find more about table-value contructor here: Table Value Constructor (Transact-SQL)

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72