How do I implement the autonumber field in SQL Server 2005?

Question

I know the IDENTITY fields, but I have the feeling that I cannot use it to solve my problem.

Say I have several clients. Each customer has several orders. Each client should have their own orders, sequentially numbered, specific to them.

Example table structure:

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

Some example lines for this table:

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

I know that a naive way would be to take the MAX ClientOrderID for any client and use this value for INSERT, but this will be subject to concurrency issues. I was considering using a transaction, but I'm not quite sure what the widest area of ​​isolation can be used for this. I will use LINQ to SQL, but I have a feeling that this is not relevant.

Answer 1

- , , , MAX() , , concurrency.

,

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;

, OrderID .

, , . URL-

Answer 2

. OrderRepository, concurrency ( , db, ).

: http://martinfowler.com/eaaCatalog/repository.html

Answer 3

( ) - , , . VendorOrderNumber, , VendorOrderNumber, .

Answer 4

, , , , .

Max (ClientOrderID) (. ). , . , , .

Nick DeVore , .

, / ClientOrderID, , :

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

This assumes that the ClientOrder identifiers are in the same sequence as the OrderID. Without actually storing the identifier, it's embarrassing to use as a key for anything else. This approach should not be dependent on data growth.