• Why not just store the sequence number in a table and perform an update statement like this (assumes a table called SequenceTable exists and has two columns, CurrentSequenceNumber [int or bigint] and SequenceYear [int]):

    update SequenceTable

    set @NewSequenceNumber = CurrentSequenceNumber + 1, CurrentSequenceNumber = @NewSequenceNumber

    where SequenceYear = year(getdate())

    This gives you the next number in the sequence and updates the sequence table in a single transaction. When the new year rolls around just add a new row to the sequence table for the new year and start the sequence at zero again.

    The update statement could also be executed from a user-defined function that concatenates the year and sequence number into a string.

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog