Generate S_No from on New GRN_ID

Analyst_SQL 3,531 Reputation points
2021-06-27T14:31:46.717+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,600 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-28T02:10:59.66+00:00

    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.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2021-06-27T15:17:51.867+00:00

    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. MelissaMa-MSFT 24,176 Reputation points
    2021-06-28T02:03:41.347+00:00

    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.


  3. Ronen Ariely 15,096 Reputation points
    2021-06-28T14:06:45.123+00:00

    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.

    0 comments No comments

  4. Ronen Ariely 15,096 Reputation points
    2021-06-28T14:33:59.997+00:00

    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

    :-)