Last week, I saw an entry in a social network in Spanish, asking for this specific case:

“I need to use a sequential numbering for an Orders table, but it must start in one for each customer. How can I accomplish this?”

Of course, it can be done by selecting the max value of the column, filtered by customer, and add 1 to that value.

However, it is a good example to implement a SEQUENCE in T-SQL, as follows:

Consider these two tables:

You must create a new sequence for each new customer added to the database, as follows:

EGIN
    BEGIN
      INSERT INTO [OnLineSales].[WebCustomers]
       (
          [LastName]
        , [FirstName]
        , [Address]
        , [City]
        , [Region]
        , [PostalCode]
        , [CountryId]
        , [Phone]
        , [Fax]
        , [email]
        , [LanguageId]
       )
      VALUES
       (
          @LastName
        , @FirstName
        , @Address
        , @City
        , @Region
        , @PostalCode
        , @CountryId
        , @Phone
        , @Fax
        , @email
        , @LanguageId
       );
    END;
    DECLARE 
           @SeqCreator  NVARCHAR(MAX) = replace(
           'CREATE SEQUENCE [OnLineSales].[WebCustomers_@@] START WITH 1 INCREMENT BY 1',
           '@@',
           IDENT_CURRENT('[OnLineSales].[WebCustomers]'));
    EXEC [sp_executesql] 
         @stmt = @SeqCreator;
  END;

Then, when a new order is inserted, you can get the next value from the sequence, using this code:

BEGIN
  DECLARE 
         @GetSeq  NVARCHAR(MAX) = 
         replace('SELECT NEXT VALUE FOR OnLineSales.WebCustomers_@@ as OrderNo', 
         '@@', @IdCustomer);
  DECLARE 
         @OrderNo  INT;
  EXEC [sp_executeSQL] 
         @GetSeq
     , N'@OrderNo INT OUTPUT'
     , @OrderNo OUTPUT;
  INSERT INTO [OnLineSales].[Orders]
     (
      [IdCustomer]
    , [OrderNo]
    , [Date]
    , [Total]
   )
  VALUES
     (
      @IdCustomer
    , @OrderNo
    , @Date
    , @Total
   );
END;

This is a simple way to keep separated numbering by other column value.

This is a simple way to keep separated numbering by other column value.

Note
As you can see, both statements samples use variables. In fact, they are parameters, since are implemented as stored procedures.
This is important, since a normal user, not and admin, dbo, or server admin, must not have DDL permissions, and the CREATE SEQUENCE statement is a DDL one.
However, been the Stored Procedure created by an admin, when it is used by another user with EXECUTE permissions, the DDL statement works without raise any error.
It is like the normal user “is impersonated” by the stored procedure’s creator, and the SP runs under the security permissions environment of the SP creator.

Below I include both procedures

/****** Object:  StoredProcedure [dbo].[WebCustomers_Add]    Script Date: 7/28/2021 12:10:15 PM ******/
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE PROCEDURE [dbo].[WebCustomers_Add]
 (
   @LastName    NVARCHAR(40)
 , @FirstName   NVARCHAR(30)
 , @Address     NVARCHAR(60)  = NULL
 , @City        NVARCHAR(15)  = NULL
 , @Region      NVARCHAR(15)  = NULL
 , @PostalCode  NVARCHAR(10)  = NULL
 , @CountryId   INT           = 1
 , @Phone       NVARCHAR(24)  = NULL
 , @Fax         NVARCHAR(24)  = NULL
 , @email       NVARCHAR(100) = NULL
 , @LanguageId  INT           = 1
)
AS
  BEGIN
    BEGIN
      INSERT INTO [OnLineSales].[WebCustomers]
       (
          [LastName]
        , [FirstName]
        , [Address]
        , [City]
        , [Region]
        , [PostalCode]
        , [CountryId]
        , [Phone]
        , [Fax]
        , [email]
        , [LanguageId]
       )
      VALUES
       (
          @LastName
        , @FirstName
        , @Address
        , @City
        , @Region
        , @PostalCode
        , @CountryId
        , @Phone
        , @Fax
        , @email
        , @LanguageId
       );
    END;
    DECLARE 
           @SeqCreator  NVARCHAR(MAX) =
           replace('CREATE SEQUENCE [OnLineSales].[WebCustomers_@@] START WITH 1 INCREMENT BY 1',
           '@@',
           IDENT_CURRENT('[OnLineSales].[WebCustomers]'));
    EXEC [sp_executesql] 
         @stmt = @SeqCreator;
  END;
GO
 
/****** Object:  StoredProcedure [OnLineSales].[Orders_Add]    Script Date: 7/28/2021 12:10:15 PM ******/
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE PROCEDURE [OnLineSales].[Orders_Add]
 (
   @IdCustomer  INT
 , @Date        SMALLDATETIME
 , @Total       SMALLMONEY
)
AS
  BEGIN
    DECLARE 
           @GetSeq  NVARCHAR(MAX) =
           replace('SELECT NEXT VALUE FOR OnLineSales.WebCustomers_@@ as OrderNo',
           '@@', 
            @IdCustomer);
    DECLARE 
           @OrderNo  INT;
    EXEC [sp_executeSQL] 
         @GetSeq
       , N'@OrderNo INT OUTPUT'
       , @OrderNo OUTPUT;
    INSERT INTO [OnLineSales].[Orders]
     (
        [IdCustomer]
      , [OrderNo]
      , [Date]
      , [Total]
     )
    VALUES
     (
        @IdCustomer
      , @OrderNo
      , @Date
      , @Total
     );
  END;
GO

Hope this help.