SQL Server Performance

Unique number

Discussion in 'General Developer Questions' started by szigic, Jun 6, 2006.

  1. szigic New Member

    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

  2. satya Moderator

    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.
  3. szigic New Member

    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
  4. Adriaan New Member

    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.
  5. FrankKalis Moderator

    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
  6. Adriaan New Member

    As long as you have indexes on documenttype, invoicenr - it shouldn't be too hard?
  7. szigic New Member


    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.
  8. szigic New Member

    Adriaan..What do you mean?
  9. Adriaan New Member

    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.
  10. szigic New Member

    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
  11. Adriaan New Member

    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.
  12. papakmondal New Member

    why don't you use auto number increment ? I think it will help you.

    Best Regards,
    Papak Mondal
  13. szigic New Member

    Adriaan..thanks, I think that approach can solve the problem
  14. merrillaldrich New Member

    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
  15. szigic New Member

    merrillaldrich...yes, thanks, I know that metod, but I'm trying to avoid unnecessary tables in database
  16. FrankKalis Moderator

    Interesting link. Thanks! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  17. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by szigic</i><br /><br />merrillaldrich...yes, thanks, I know that metod, but I'm trying to avoid unnecessary tables in database<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">... unless they are proved necessary [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page