Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: generate next sequence no. issue

    We have created a control table to generate the next sequence no.

    AA_TBL: BRANCH, YEAR_NO, SEQ_NO, TIMESTAMP
    Unique key is BRANCH, YEAR_NO and SEQ_NO

    Note that for each combination of BRANCH & YEAR_NO, the SEQ_NO starts from 1.
    So there will be duplicate seq_no for different branch & year_no. Hence IDENTITY column will not work here.

    There are 2 options for us to generate the next sequence no.

    Option 1 :
    SELECT (SEQ_NO + 1) AS NEW_SEQ_NO
    FROM AA_TBL
    WHERE BRANCH = 002
    AND YEAR_NO = 2003;

    UPDATE AA_TBL
    SET SEQ_NO = NEW_SEQ_NO
    WHERE BRANCH = 002
    AND YEAR_NO = 2003;

    So, for each branch & year_no, there is only 1 SEQ_NO.

    Option 2:
    SELECT MAX(SEQ_NO) + 1 AS NEW_SEQ_NO
    FROM AA_TBL
    WHERE BRANCH = 002
    AND YEAR_NO = 2003;

    INSERT INTO AA_TBL(002, 2003, NEW_SEQ_NO, DEFAULT);

    So, for each branch & year_no, there are a lot of SEQ_NO.

    Which method is better ?

    rgds,
    eric ng

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Eric,
    I am not sure which one is "better". That depends on your perspective. That being said, we are using the first scenario here and these are the reasons.

    1) concurrency. When we read the current value, we read it "FOR UPDATE" so that we lock the row. Then we do our increment and update, and the rest of the Unit of Work (UOW). This will prevent concurrent users from trying to increment to the same value.

    2) space. There is only one row for each search criteria (in your case BRANCH & YEAR_NO). Table will be smaller.

    3) performance. Because of (2) above, we do not need to use the aggregate function MAX, which may read more rows than the one we need.

    HTH

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •