SQL Server Performance Forum – Threads Archive

Unique number

I’m using several different document types in one table and I need unique number for all of that document types (invoice number, purch.order number..). To provide that I’m using sqlserever func max(some_numeric_field) + 1 to provide next number. In a network environment with meny users sometimes happend that in the same time 2 users get the same number. I know how to avoid duplicate numbers using constraints or unique indexes, but does anybody know how to provide a solution for unique number using row locking or something else? Thanks in advance and best regards, Sasha

Why not to have identity number in this case? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Because I use one column for several document types, for exaple max invoice number is 65543 and max purch.order number is 74567. Of course I have another column for document type to know for what document type max number I’m looking for

Add the unique number only after inserting the new record, for instance through an insert trigger that has an explicit transaction around the lookup, calculation, and update stuff.

You would have to use SELECT along with an UPDATE lock to ensure that the number you’ve selected is actually the highest after you’ve incremented it and update the table again. May I say that this can get extremely slow for heavily inserted tables. You’ll get a whole bunch of locking and blocking. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

As long as you have indexes on documenttype, invoicenr – it shouldn’t be too hard?

Adriaan… I will try this approach but I think at the end the resault will be the same, before or after inserting, because you can do nothing within inserting record in the table and that filed then must allow nulls. FrankKalis…Yes, I agree with you that some kinds of locks can trigger slow performance.

Adriaan..What do you mean?

My previous response was in follow-up to Frank’s suggestion. Yes, you would need the column to allow nulls. But you have to realize that as long as you have a trigger, the insert is not committed until the trigger has finished. If necessary, you can let the trigger fail with a ROLLBACK TRANSACTION instruction, which will also make the insert fail. If you create the column for the invoice number as InvoiceNr VARCHAR(30) NULL UNIQUE then one row with a NULL is acceptable at any one time.

Do you mean something like this? BEGIN TRANS SELECT @MaxNum = MAX(document_number)
WITH (ROWLOCK)
FROM Documents INSERT INTO Documents (documents_number)
VALUES (@MaxNum + 1) COMMIT TRANS

No, my idea was to generate the document number in an insert trigger on the table, using an UPDATE statement. So you insert into the table with a null for the document number, and the insert trigger does the code that you have – except that your INSERT INTO would become an UPDATE joining the Documents table to the Inserted conceptual table to filter for the new row.

why don’t you use auto number increment ? I think it will help you. Best Regards,
Papak Mondal

Adriaan..thanks, I think that approach can solve the problem

szigic – There’s a faster method described here: http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx Option #2. What you do is create a small table for each doc type, each of which has an identity column (each doc type then has its own sequence.) An insert then works by
(a) insert into one of the sequence tables according to doc type
(b) select scope_identity() as your next id
(c) insert with that value into your "real" table

merrillaldrich…yes, thanks, I know that metod, but I’m trying to avoid unnecessary tables in database

Interesting link. Thanks! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by szigic merrillaldrich…yes, thanks, I know that metod, but I’m trying to avoid unnecessary tables in database <hr height="1" noshade id="quote"></blockquote id="quote">… unless they are proved necessary [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>