question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft commented

Generate S_No from on New GRN_ID

I want when GRN_ID is change ,then S_NO must start from 1, then do increment of one on every insertion in database ,until unless GRN_ID is not get change.
it should be auto generated S_NO.

below is data in which i have given two grn_ID ,S_NO is getting change

 Create table #tbl_GRN_Detail (GRN_ID int,Item_Code int,Item_Weight int,S_NO int)
    
    
 insert into #tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,S_NO ) values (1 ,1,5000,1)
 insert into #tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,S_NO ) values (1 ,2,410,2)
 insert into #tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,S_NO ) values (1 ,7,333,3)
    
    
 insert into #tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,S_NO ) values (2 ,11,5001,1)
 insert into #tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,S_NO ) values (2 ,24,411,2)
 insert into #tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,S_NO ) values (2 ,74,331,3)



109681-image.png


sql-server-generalsql-server-transact-sql
image.png (6.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @akhterhussain-3167

Could you please validate all the answers so far and provide any update?

If both are not working, please provide more sample data and expected output.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @akhterhussain-3167

You could also try with below function:

One: create one function before creating table

 CREATE FUNCTION dbo.GenerateSNO
 (
     @Row_Id int,
     @GRN_ID int
 )
 RETURNS Int
 AS
 BEGIN
    
     RETURN 
     (
         SELECT COUNT(*)
         FROM dbo.tbl_GRN_Detail
         WHERE GRN_ID = @GRN_ID
         AND Row_Id <= @Row_Id
     )
 END

Two: create table dbo.tbl_GRN_Detail

 CREATE TABLE dbo.tbl_GRN_Detail
 (
     Row_Id [int] IDENTITY(1,1),
     GRN_ID int,
     Item_Code int,
     Item_Weight int,
     S_NO AS dbo.GenerateSNO(Row_Id, GRN_ID), 
     CONSTRAINT PK_SNO PRIMARY KEY (Row_Id)
 )
 GO

Three: insert some rows

 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (1 ,1,5000)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight ) values (1 ,2,410)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (1 ,7,333)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (2 ,11,5001)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (2 ,24,411)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (2 ,74,331)

Four: check the result

 select * from  dbo.tbl_GRN_Detail

Output:

 Row_Id    GRN_ID    Item_Code    Item_Weight    S_NO
 1    1    1    5000    1
 2    1    2    410    2
 3    1    7    333    3
 4    2    11    5001    1
 5    2    24    411    2
 6    2    74    331    3

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered akhterhussain-3167 commented

Hi

This is exactly why we have the built in function ROW_NUMBER :-)

 SELECT GRN_ID ,Item_Code,Item_Weight,S_NO, ROW_NUMBER() OVER (PARTITION BY GRN_ID order by Item_Code)
 FROM tbl_GRN_Detail
 GO



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@pituach ,

I want to insert S_No into database,i know Row_number() is increment serial no. but do not know how to insert into database, at the time of insert

0 Votes 0 ·

@pituach ,

I want to insert S_No(Row_number()) into database,i know Row_number() is increment serial no. but do not know how to insert into database, at the time of insert

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @akhterhussain-3167

You could have a try to create one trigger as below:

One: create table tbl_GRN_Detail

  drop table if exists tbl_GRN_Detail    
  Create table tbl_GRN_Detail (GRN_ID int,Item_Code int,Item_Weight int,S_NO int)

Two:create one trigger

 CREATE TRIGGER inserttbl_GRN_Detail ON tbl_GRN_Detail  
 AFTER INSERT  
 AS
 BEGIN  
     UPDATE tbl_GRN_Detail 
     SET S_NO=(SELECT COUNT(*) FROM tbl_GRN_Detail A
     INNER JOIN INSERTED B ON A.GRN_ID=B.GRN_ID)
     WHERE S_NO IS NULL
 END

Three: insert some rows

 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (1 ,1,5000)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight ) values (1 ,2,410)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (1 ,7,333)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (2 ,11,5001)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (2 ,24,411)
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (2 ,74,331)

Four: check the result

  select * from tbl_GRN_Detail

Output:

 GRN_ID Item_Code Item_Weight S_NO
 1 1 5000 1
 1 2 410 2
 1 7 333 3
 2 11 5001 1
 2 24 411 2
 2 74 331 3

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day @MelissaMa-msft

This solution is very problematic!

Unfortunately the comment do not allow me to write the explanation as it limited to 1000 char.

Please check my answer bellow.

0 Votes 0 ·

@pituach

why solution is problematic

0 Votes 0 ·
pituach avatar image pituach akhterhussain-3167 ·

Hi

took me several minutes to write the answer. Please check it now :-)

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

short discussion on first suggested solution using TRIGGER AFTER INSERT


This solution is very problematic!

(1) If two INSERT actions will be execute at the same time then the calculation of S_NO can produce the same values. You might end up with duplicate values.

By default, SQL Server do not use TABLOCK when INSERT is executed unless you use hint or if you're doing certain kinds of bulk load operations. In order to use this solution you must use exclusive lock on the entire table. This will enforce only one insert at a time which means potentially dramatic reduce in performance in some cases.

Moreover, using AFTER TRIGGER means that you have two actions under the same transaction: INSERT+UPDATE. In this case the INSERT can be blocked by the UPDATE of another transaction and the UPDAT can be blocked by the INSERT of this transaction which lead to deadlock.

Note! in small tables you will not probably not noticed it as the lock will be on the page and all your data will be on the same page. But on real cases you will need to confirm a lock of the entire table as I mentioned above.

(2) In addition, what if the someone INSERT a row with Item_Code smaller than the maximum Item_Code in the table?!? The order of the S_NO should be changed according to the new order?!? This is something the OP should clarified.

(3) In @MelissaMa-msft original post he used recursive CTE which he change. This is good fix! since it make no sense to execute a query as such which includes loops ON THE ENTIRE TABLE which cost a lot of resource, each time that you insert new row. This calculation of the S_NO value each time from scratch is VERY problematic.

(4) This solution use COUNT() in order to find the next value which make no sense since rows maybe were deleted and the COUNT() will return low value which will produce a lower S_NO value from one that already exists.

Simple example: DELETE a row and next INSERT will create duplicate values

 DELETE tbl_GRN_Detail WHERE Item_Code = 1
 GO
 insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight) values (1 ,1,5000)
 GO
 select * from tbl_GRN_Detail
 GO

This can be fix in no time by using MAX instead of using COUNT.

short discussion on second suggested solution using FUNCTION and COMUTED COLUMN


This solution using commuted column does not have the same issues as first solution

With that being said, base the solution on FUNCTION will be execute on each row separately which is a real performance issue.

If you are not using PERSISTED then the data is not really stored in table. This is like a sub query and when you will SELECT multiple rows for example, then it will be executed including the entire aggregation action on the entire table. This might be a killing performance.

Unfortunately you cannot use PERSISTED since the column/function is is non-deterministic (which is another issue). Therefore, why do we need to use COMUTED COLUMN instead of finding the value of the S_NO on the fly when we need it. It is not like we can index by this column since it is not PERSISTED.

So what next?


For most cases I would recommend not to have this information in the table, but on the fly when we need it in the query level.

--> If you must have it stored in the table then you can use "INSTEAD OF TRIGGER" instead of "AFTER TRIGGER".

Why?
(1) Using INSTEAD OF TRIGGER we ignore the original INSERT and we execute a new INSERT query. This means that there is no need for two queries INSERT+DELETE.
(2) We can explicitly lock the table even if the user did not thought about it, simply by using the hint WITH (TABLOCK) in our new INSERT query. This mean that we can keep consistency but we still get reduce of performance as we do eliminate the parallel INSERTs (as I said, for most cases I would not use any of this request and will suggest to find the S_NO on the fly in the query level).

--> We will NOT use COUNT but MAX in order to find the current maximum value of S_NO.

Here is a simple option using INSTEAD OF TRIGGER



 drop table if exists tbl_GRN_Detail
 GO
    
 Create table tbl_GRN_Detail (GRN_ID int,Item_Code int,Item_Weight int,S_NO int)
 GO
    
 CREATE OR ALTER TRIGGER inserttbl_GRN_Detail ON tbl_GRN_Detail  
 INSTEAD OF INSERT AS BEGIN
  SET NOCOUNT ON;
    
  ;WITH MyCTE01 AS (
  SELECT t.GRN_ID,MS = ISNULL(MAX(t.S_NO),0)
  FROM tbl_GRN_Detail t
  GROUP BY t.GRN_ID
  ),
  MyCTE02 AS (
  SELECT i.GRN_ID ,i.Item_Code,i.Item_Weight, MS = ISNULL(t.MS,0)
  FROM INSERTED i
  LEFT JOIN MyCTE01 t ON i.GRN_ID = t.GRN_ID
  )
  insert into tbl_GRN_Detail WITH (TABLOCK) (GRN_ID ,Item_Code,Item_Weight, S_NO)
  SELECT GRN_ID ,Item_Code,Item_Weight, MS + ROW_NUMBER() OVER (PARTITION BY GRN_ID ORDER BY Item_Code)
  FROM MyCTE02
 END
 GO


Again! for most cases I would prefer not to use any "solution" but get the value of S_NO on the fly using my first answer in this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach commented

By the way, if the number of GRN_ID (which represent the group that we want to have increment value of S_NO for this) then we can have a much much better solution and simpler to implement without triggers.

The big (but only) disadvantage for this solution is that this is hard coded for specific range of GRN_ID.

For example, if we know that GRN_ID can only one of those value: 1,2,3

Then we can simply create three SEQUENCE and in the table we can use the selected SEQUENCE according to the value of the GRN_ID

:-)



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can you provide complete example ,so i can understand better and learned

0 Votes 0 ·
pituach avatar image pituach akhterhussain-3167 ·

I gave you a complete sample under the title "Here is a simple option using INSTEAD OF TRIGGER"

Do you mean sample of using SEQUENCE ?!?

As I mentioned this fit only if you have specific values of GRN_ID and you know that you will not have any other value. If someone will insert GRN_ID which is not one of these then using SEQUENCE cannot solve your need. If someone will need to add a different value for GRN_ID then you will need to create a new SEQUENCE.

This solution fits only if you have very small amount of GRN_ID and you have a table which stores all these values => in this case when someone create a new GRN_ID then we can create a new SEQUENCE using TRIGGER, but this not fit to for multiple GRN_ID since you need a SEQUENCE for each GRN_ID.

So according to your current description you can use the solution I gave you with full code or make sure your database structure and needs fit for using SEQUENCEs as solution.

0 Votes 0 ·

Fix: NOT "without triggers" (typo). I meant without locking. With simple triggers and without locking the table since using SEQUENCE will manage the multiple users for us.

Note! SEQUENCE does not guarantees consecutive numbers (a bit like IDENTITY)

0 Votes 0 ·