Customer1  (Order #s 1,2,3,4,5...)
Customer2  (Order #s 1,2,3,4,5...)
	
BEGIN TRANSACTION
  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION
	
TABLE CustomerNextOrderNumber
{
    CustomerID id PRIMARY KEY,
    NextOrderNumber int
}
	
BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

SELECT @NextOrderNumber = NextOrderNumber
FROM  CustomerNextOrderNumber (UPDLOCK)
WHERE CustomerID = @CustomerID

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID


... use number here


COMMIT
	
BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID

SELECT @NextOrderNumber = NextOrderNumber
FROM CustomerNextOrderNUmber
where CustomerID = @CustomerID
	
COMMIT
	
Order Description Date Due
1     Staples     26/1/2012
2     Stapler     1/3/2012
3     Paper Clips 19/1/2012
	
BEGIN TRANSACTION
SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION
	
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
...
	
SELECT @NewOrderNumber = MAX(OrderNumber)+1 
From Orders with (tablockx, holdlock, updlock)
where CustomerID=@ID
	
create table TestIds
(customerId int,
nextId int)

insert into TestIds
values(1,1)
insert into TestIds
values(2,1)
insert into TestIds
values(3,1)

go

create proc getNextId(@CustomerId int)
as

declare @NextId int

while (@@ROWCOUNT = 0)
begin
    select @NextId = nextId
    from TestIds
    where customerId = @CustomerId

    update TestIds
    set nextId = nextId + 1
    where customerId = @CustomerId
    and nextId = @NextId

end

select @NextId  
go
	
BEGIN TRANSACTION
  SELECT ID
  FROM Customer WITH(ROWLOCK)
  WHERE Customer.ID = @ID

  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION