Orders:
OrderID | ClientID | ClientOrderID | etc...

OrderID | ClientID | ClientOrderID | etc...
1 | 1 | 1 | ...
2 | 1 | 2 | ...
3 | 2 | 1 | ...
4 | 3 | 1 | ...
5 | 1 | 3 | ...
6 | 2 | 2 | ...

select @newOrderID=max(ClientOrderID) + 1
from orders
where clientid=@myClientID;

insert into ( ClientID, ClientOrderID, ...)
values( @myClientID, @newOrderID, ...);

insert into ( ClientID, ClientOrderID, ...)
select @myClientID, max(ClientOrderID) + 1, ...
from orders
where clientid=@myClientID;

SELECT *,
ROW_NUMBER() OVER(ORDER BY OrderID) AS ClientOrderID
FROM Orders
WHERE ClientID = 1