Custom Sequence Numbering

  • I agree that the UI layer should be passing the correct combination, and there is a problem if it is not. From a defensive programming standpoint, you have created a procedure that will not operate correctly if bad parameters are passed, which leaves the system open to problems. I suggest you have two choices.

    Option 1 is to exclude the PersonID from the parameters. The procedure doesn't need the UI to pass it because the value can be looked up from the ToDoID. Without the parameter, there is no possibility that the UI will pass the incorrect value. You have eliminated a possible source of error.

    Option 2 is to validate the PersonID against the value in the ToDoList table. If the UI passes the incorrect value for some reason, you want the database to pass that error back to the UI so the developer knows that incorrect values were passed and can fix the UI. A junior programmer may have made a mistake, but will never see it because he never gets feedback that the mistake was made. On top of that, the procedure will either not perform the intended action or will perform an incorrect action based on bad parameters.

    I would choose Option 1, which eliminates a source of error and eliminiates the need to validate a parameter.

  • Hi Jonathan,

    Unfortunately the two systems that this is implemented in are seriously high volume OLTP systems and so the extra cost of defensive programming has been vetoed - plus we have no junior developers! I do take your point though and I would probably go for a option 3 (a new option similar to your option 1 but one that doesn't require an additional lookup): this would be to check the @@ROWCOUNT after the update and raise an error if it was zero - this would tell the caller that there had been some kind of issue without incurring too much extra overhead. Tables with sequences like this are generally very active, so the least time taken up with sequencing the better - hence the complicated "all in one hit" update statement!

    This has given me food for thought though, as in future articles I will now include some defensive programming, with a comment that if performance is paramount then this can be removed.

    Thanks again for taking the time to comment on this.

    Cheers, James

    MCM [@TheSQLPimp]

  • The main idea of keeping everything in sequence is ofcourse always a performance issue as changing one record implicitly means an update to all following records, which is implicitly expensive and more so for larger tables.

    To minimize the cost, I see two options:

    * Keep the sequence numbers in a separate table with a 1 to 1 relationship to the main table. So there is a second table with the same PK as for the original table, and it has one extra attribute, namely a sequence counter with a unique index on it.

    * Keep a seprarate numbers with table the same number of records as the main table and let it have a foreign key to the main table. The primary key is the sequence number and it also has a unique index on the foreign key in this instance. You never have gaps this way and never delete records other then the last record(s) in the clustered index. Order changes affect only updates to the foreign key on existing pages.

    The static nature of the last method should work particulary well combined with with view- or table-partitions.

    I can think of more complex schemes that reduce things ever further by eliminating a lot of updates, and by localizing them. This is best pictured as working with offsets relative to a reference value. By updating the reference value(s), all following records are renumbered automatically without update. To reference the effective sequence number or perform sorts, you reference both the reference and the offeset to get the final sequence number/ordering. In such a scheme, moving a record to the top of the list only requires updating one or more reference records, and sometimes some of the offsets as well. But never all the offset records at once that exist for every records in the main table.

  • Interesting Peter but you still then have the overhead of maintaining the base table upon insert, update and delete. I think you'd have to test with both scenarios to ascertain the impact.

    Nice to know the article is making people think!

    Cheers, James

    MCM [@TheSQLPimp]

  • Interesting article. However, the code in the article is basically re-numbering manually instead of taking advantage of the built in Row_Number() functionality.

    This seems to be a lot simpler:

    (Note: I changed the table to a temp table for easier testing)

    -- create the sequence test table

    create table #ToDoList


    todoID int identity(1,1) not null primary key,

    PersonID int not null,

    todoText nvarchar(200) not null,

    todoSequence smallint not null default(0)



    -- populate the sequence test table

    insert into #ToDoList(PersonID, todoText, todoSequence)

    select 1, 'Task 0', 0 union all

    select 1, 'Task 1', 1 union all

    select 1, 'Task 2', 2 union all

    select 1, 'Task 3', 3 union all

    select 1, 'Task 4', 4 union all

    select 1, 'Task 5', 5 union all

    select 1, 'Task 6', 6 union all

    select 1, 'Task 7', 7 union all

    select 1, 'Task 8', 8 union all

    select 1, 'Task 9', 9


    SELECT *

    FROM #ToDoList

    -- resequence tasks

    DECLARE @PersonID BIGINT = 1

    , @ToDoID INT = 1

    , @NewSeq INT = 9

    -- figure out the direction we are moving in

    -- down = when the new sequence variable is greater than the old sequence variable

    -- up = when the new sequence variable is less than then the old sequence variable

    DECLARE @Down BIT -- 1 means down, 0 means up

    SELECT @Down = CASE WHEN todoSequence < @NewSeq -- move down

    THEN 1

    ELSE 0 -- move up


    FROM #ToDoList

    WHERE todoID = @todoID

    ;WITH NewSequences AS


    SELECT *


    CASE WHEN TDL.toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END

    -- order by new sequence without ordering, two numbers will have the same seq

    , CASE WHEN TDL.toDoID = @ToDoID THEN @Down ELSE ~@Down END)

    -- pick with of the two will be first, based on wether we are moving up/down

    - 1 -- for 0 based list

    AS RowNum

    FROM #ToDoList TDL

    WHERE PersonID = @PersonID



    SET TDL.todoSequence = NS.RowNum

    FROM #ToDoList TDL

    JOIN NewSequences NS ON TDL.ToDoID = NS.ToDoID

    SELECT *

    FROM #ToDoList

    ORDER BY todoSequence

    -- clean up

    DROP TABLE #ToDoList

  • good article; just a quick note on the insert statement; SQL 2008 allows multiple values in one INSERT statement and is quite faster than an "INSERT... SELECT... UNION ALL":

    INSERT INTO Table ( Column1, Column2 ) VALUES

    ( Value1, Value2 ), ( Value1, Value2 )...

  • Well, I was not as satisfied with rownumber over order-by ..

    I needed to enumerate 22mio records and over 3h didn't know wheather I'm on half way or no-way.

    So I used an alternative:

    1. original table: T(n int, data char..) with 22mio records

    2. copy top 0 into T' from T

    3. alter table T': make n as autonumber

    4. insert into T' from T

    if 4th step fails because of transaction lenght, use some criteria on T to only get

    part of data, i.e. year(date), etc.

    Worked fine.


Viewing 7 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply