How do I mimic autoNumber for non-identity columns?

Assume I have an inventory system used by several customers. Each customer
wants each item in their inventory to have a number, and they don't want any
gaps.
So, there's an Item table that has information about each item, as well as
the customer the item belongs to. When an item is inserted, I cannot use an
Identity column to autonumber the ItemId because if a customer inserts an
item, then a second customer inserts a hundred items, then the original
customer inserts another item, there is a gap of one hundred items from the
perspective of the original customer. This isn't desired behavior.
I need a way to do a per-customer autonumber, but I don't know how to do
this without running into concurrency problems.Greg, If I understood you properly you need
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE custid=.....
INSERT INTO AnothetTable VALUES (@.max_item)
COMMIT TRAN
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
> Assume I have an inventory system used by several customers. Each
> customer
> wants each item in their inventory to have a number, and they don't want
> any
> gaps.
> So, there's an Item table that has information about each item, as well as
> the customer the item belongs to. When an item is inserted, I cannot use
> an
> Identity column to autonumber the ItemId because if a customer inserts an
> item, then a second customer inserts a hundred items, then the original
> customer inserts another item, there is a gap of one hundred items from
> the
> perspective of the original customer. This isn't desired behavior.
> I need a way to do a per-customer autonumber, but I don't know how to do
> this without running into concurrency problems.|||If Table can be the same as AnotherTable, I think this could work. So,
assuming ItemNumber is the column I want to mimic autonumber on, we'd have:
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(ItenNumber),0) FROM Inventory WITH
(UPDLOCK,HOLDLOCK)
WHERE custid=4
INSERT INTO Inventory VALUES (@.max_item + 1)
COMMIT TRAN
Would that work? Are UPDLOCK and HOLDLOCK merely hints? What if the hints
get ignored?
Thanks.
"Uri Dimant" wrote:

> Greg, If I understood you properly you need
> DECLARE @.max_item
> BEGIN TRAN
> SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE custid=.....
> INSERT INTO AnothetTable VALUES (@.max_item)
> COMMIT TRAN
>
>
> "Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
> news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
>
>