• First.

    The Idea of a Sequence table is the right idea in this case.

    Second.

    "Central location for keys is a concurrency nightmare" if you are not using the appropriate locking mechanisms.

    ex:

    • do not compute the sequence year on the query
    • Should you need more than one at a time add a @count param and get only the latest
    • if you have multiple sequences make sure you have a primary key on the sequencing key
    • should the table grow make sure that the update is using (with rowlock)
    • Last but not least if the number of proccess competing for keys is very ( and I mean very ) high use sp_tableoption and "pin" the sequence table.

    As you can see I have experience with this structure and have been successfuly able to implemet such construct.

    It is not my preffered way but it is very portable and very flexible

    Cheers,

     

     


    * Noel