Generate S_No from on New GRN_ID

Question

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)  

Answer 1

Hi @Analyst_SQL ，

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.

Answer 2

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  

Answer 3

Hi @Analyst_SQL ，

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.

Answer 4

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.

Answer 5

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

:-)