title: SQL Server - Create a New ID "group by column"
body: <p>I have 2 tables</p>
  
  <p><strong>Order</strong></p>
  
  <pre><code>OrderID INT
  ...
  </code></pre>
  
  <p><strong>OrderItems</strong></p>
  
  <pre><code>OrderIemID INT
  OrderID INT
  ItemNO INT
  </code></pre>
  
  <p>What I want is <code>ItemNO</code> starts at 1 for each <code>OrderID</code> and increases by 1 for each new <code>OrderIemID</code> in <code>OrderItems</code>.</p>
  
  <p>I added this into the insert trigger. But is it possible to do this in a different way? UDF and calculate column, and use persistent?</p>
  
  <hr>
  
  <h2>UDF:</h2>
  
  <pre><code>ALTER FUNCTION [dbo].[afnc_Proc_CreateNewOrderItemsItemNo] (
   @OrderID INT
  ) RETURNS INT
  AS
  BEGIN
  
      DECLARE @NewItemNo INT 
  
      SET @NewItemNo=1
  
      SELECT TOP 1 @NewItemNo = ItemNo + 1
          FROM dbo.atbl_Proc_OrderItems
          WHERE OrderID=@OrderID
           ORDER BY ItemNo DESC
  
      RETURN @NewItemNo
  END
  </code></pre>
  
  <hr>
  
  <h2>Trigger:</h2>
  
  <pre><code> UPDATE U   
      SET ItemNO=dbo.afnc_Proc_CreateNewOrderItemsItemNo(I.OrderID)
      FROM atbl_Proc_OrderItems AS U
      INNER JOIN inserted I ON U.PrimKey=I.PrimKey   
  </code></pre>