• lcasamen (2/7/2011)


    So I realize this topic is a bit old but I'm in the middle of converting a java app from Oracle to SQLServer and of course there is a ton of bad code that fetches the next value and then uses it to create data - sometimes creating the child before the parent ( ugh). Of course there is virtually no time to get this converted. I've got 20 years of Oracle experience but am new to SQLServer - and seriously worried about the difference in locking behavior.

    Anyway - we ran the conversion tool from MSDN and it came up with this for sequence generation:

    1. create 1 table per sequence - ie

    create table Z_A_SEQ_STBL(nextval numeric(10, 0) identity(5000,1) NOT NULL);

    2. create this proc:

    create procedure SL_A_SEQ_NEXTVAL(@nextval [numeric](10, 0) out) as BEGIN insert into Z_ACCESSORY_SEQ_STBL default values set @nextval = scope_identity() END

    ;

    Doesn't look nearly as robust as the one in this thread. What do you guys think?

    If you're able to, forget about sequence tables in SQL Server. Use an IDENTITY column on the tables, instead.

    I will say, however, that if you don't mind the table growing, you can certainly use the method you've shown without much chance of a deadlock. Of course, that would also be true for an IDENTITY column on the final table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)