Custom Sequence Numbering

  • Comments posted to this topic are about the item Custom Sequence Numbering

    James
    MCM [@TheSQLPimp]

  • Hi, It was a nice article. but I believe that personID column is missing in the table definition. Sorry, if I am wrong.

  • Vipul Tyagi (2/7/2011)


    Hi, It was a nice article. but I believe that personID column is missing in the table definition. Sorry, if I am wrong.

    I spotted the same.

    I presume that the field todoGroup (which is never used)declared in the original table should be PersonID instead.

  • You are correct, my apologies. I did change the todoGroup column to be personID to make it more relevant but obviously forgot to change the table create script.

    This will teach me not to make last minute changes! Rest assured that this will not happen again. Thank you for the prompt feedback.

    Regards, James (Author)

    Correct create and populate sample table SQL:

    -- create the sequence test table

    create table dbo.ToDoList

    (

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

    personID int not null,

    todoText nvarchar(200) not null

    )

    go

    -- populate the sequence test table

    insert into dbo.ToDoList(personID, todoText)

    select 1, 'Task 0' union all

    select 1, 'Task 1' union all

    select 1, 'Task 2' union all

    select 1, 'Task 3' union all

    select 1, 'Task 4' union all

    select 1, 'Task 5' union all

    select 1, 'Task 6' union all

    select 1, 'Task 7' union all

    select 1, 'Task 8' union all

    select 1, 'Task 9'

    go

    -- add a custom sequence number

    -- add the new column

    alter table dbo.ToDoList

    add todoSequence smallint not null default(0)

    go

    -- now add the initial sequence

    update dbo.ToDoList set

    todoSequence = n_seq.newSequence

    from dbo.ToDoList tdl

    inner join

    (

    selectROW_NUMBER() over (partition by personID order by personID, todoID) - 1 as newSequence,

    todoID,

    personID

    from dbo.ToDoList

    ) n_seq

    on tdl.todoID = n_seq.todoID

    GO

    James
    MCM [@TheSQLPimp]

  • Great article. It got me thinking, which first thing on a Monday morning is usually impossible! ๐Ÿ˜€

    I rewrote the CASE statement, though I haven't extensively tested this - found a bug so I'll remove it for now.

    And added more than 1 group to test:

    -- create the sequence test table

    create table dbo.ToDoList

    (

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

    PersonId int not null,

    todoText nvarchar(200) not null

    )

    go

    -- populate the sequence test table

    insert into dbo.ToDoList(PersonId, todoText)

    select 1, 'Task 0' union all

    select 1, 'Task 1' union all

    select 1, 'Task 2' union all

    select 1, 'Task 3' union all

    select 1, 'Task 4' union all

    select 1, 'Task 5' union all

    select 1, 'Task 6' union all

    select 1, 'Task 7' union all

    select 1, 'Task 8' union all

    select 1, 'Task 9'

    go

    insert into dbo.ToDoList(PersonId, todoText)

    select 2, 'Task 0' union all

    select 2, 'Task 1' union all

    select 2, 'Task 2' union all

    select 2, 'Task 3' union all

    select 2, 'Task 4'

    go

    SELECT * FROM dbo.ToDoList

    go

    alter table dbo.ToDoList

    add todoSequence smallint not null default(0)

    GO

    -- now add the initial sequence

    update dbo.ToDoList set

    todoSequence = n_seq.newSequence

    from dbo.ToDoList tdl

    inner join

    (

    select ROW_NUMBER() over (partition by personID order by personID, todoID) - 1 as newSequence,

    todoID,

    personID

    from dbo.ToDoList

    ) n_seq

    on tdl.todoID = n_seq.todoID AND tdl.personid = n_seq.personid

    GO

    SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence

    GO

    -- move Line 8 to sequence number 2

    exec dbo.[setTodoSequence]

    @personID = 1,

    @todoID = 9,

    @todoSequence = 2

    GO

    SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence

    GO

    exec [dbo].[setTodoSequence]

    @personID = 1,

    @todoID = 1,

    @todoSequence = 9

    go

    SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence

    GO

    DROP TABLE dbo.ToDoList

    Appreciate the article and the new tool in my arsenal.

    Measure twice, cut once

  • Glad you like it! I'd test with your new case statement but I think you'll have to change it a bit, as you need to cater for rows going down in sequence as well as up (so +1 will sometimes need to be -1). As you can tell, it took a whole heap of testing before I was happy with mine!

    James

    James
    MCM [@TheSQLPimp]

  • Definitely needs more testing, thanks. It will give me something to think about today ๐Ÿ™‚

    Measure twice, cut once

  • Scaling will always be a problem when resequencing with stored procedures , even with partitioning. At least this is not part of trigger design. One would use this feature sparingly.

  • Indeed but, for those people that have a need to implement this functionality, it saves both band width and processing time, both on the application and the database server. If we were doing this via a dataset returned from the client with say twenty records in, you'd be looking at twenty individual updates, with all the locking and extra processing time this would require. The solution in the article requires only one call to the database and does not need the rest of the data in order to do the resequencing.

    I would agree that scalability would be an issue if what was being resequenced was a public list that many people were updating simultaneously but the article is aimed more at business applications, where these are user-maintained lists and, as such, they are unlikely to be updated by more than one person at any given time. From personal experience I can say that this does scale well, indeed far better than a recordset based solution.

    James
    MCM [@TheSQLPimp]

  • That seems quite a bit more complicated than this:

    CREATE PROCEDURE [dbo].[setTodoSequence]

    (

    @personID int,

    @todoID as int,

    @todoSequence as smallint

    )

    AS

    SET NOCOUNT ON;

    declare @oldTodoSequence smallint

    declare @direction smallint

    declare @start smallint

    declare @end smallint

    -- get the old sequence

    set @oldTodoSequence = (select todoSequence from dbo.ToDoList where todoID = @todoID);

    SET @direction = Sign(@todosequence - @oldtoDoSequence)

    SELECT @start = CASE WHEN @direction = -1 then @todosequence ELSE @oldtodosequence END

    , @end = CASE WHEN @direction = -1 then @oldtodosequence ELSE @todosequence END

    update todolist

    set todosequence = CASE WHEN todoid = @todoID THEN @todoSequence ELSE todosequence - @direction END

    where todosequence between @start and @end

    and personId = @personid

  • The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. Your proc works fine but you won't end up with continuous sequence numbers starting at zero. If you had say thirty items in the list and you randomly deleted fifteen, you would still have gaps between your sequence numbers (as you may have deleted all the rows sequenced between 5 and 15). Your proc would indeed get the sequencing correct but still with the gaps in the numbers, whereas mine would remove the gaps. Basically, mine takes the actual row count into effect, so you get back a sequence number range equal to zero to the row count -1. Admitedly this may be a bit over the top but I hate gaps in sequence numbers and know these will occur due to deletes. So I don't just act on the existing sequence numbers, I do the required move and then adjust them to be continuous.

    Good looking proc though. Nice to see classy SQL.

    Cheers, James

    James
    MCM [@TheSQLPimp]

  • The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. Your proc works fine but you won't end up with continuous sequence numbers starting at zero. If you had say thirty items in the list and you randomly deleted fifteen, you would still have gaps between your sequence numbers (as you may have deleted all the rows sequenced between 5 and 15). Your proc would indeed get the sequencing correct but still with the gaps in the numbers, whereas mine would remove the gaps. Basically, mine takes the actual row count into effect, so you get back a sequence number range equal to zero to the row count -1. Admitedly this may be a bit over the top but I hate gaps in sequence numbers and know these will occur due to deletes. So I don't just act on the existing sequence numbers, I do the required move and then adjust them to be continuous.

    Good looking proc though. Nice to see classy SQL.

    Cheers, James

    James
    MCM [@TheSQLPimp]

  • Blimey, well, it proves that old problems never actually go away but the code to deal with them just evolves.

    Current reading up on the Merge command.... Thank you for pointing me at this. I'm never afraid to be shown a better solution; indeed, if this never happenned then I'd never learn anything!

    Cheers, James

    James
    MCM [@TheSQLPimp]

  • I have implemented a solution very similar to the one in Ten Centuries post for a similar problem. I think it is simpler, more elegant and easier to read than your solution. Please donโ€™t read that as insult as I think that your solution is more robust for the reason that you described and can understand the corresponding increased complexity.

    However, it would probably be beneficial to anyone reading if you had included a description of that added functionality in the original post. Also, as you have written your proc, there is no way to take advantage of that functionality so the reason you are doing it the way you are canโ€™t really be capitalized upon. The setTodoSequence proc only provides a way to change the sequence, not delete a block of items, so there is no reason to have that stored procedure be more complicated than a simpler update statement with the appropriate condition and range testing.

    I do think there is good information here and I have benefitted from seeing row_number() used in this way.

  • Thank you for the kind comments, they are all very useful. This is the first article I have tried to write and judging by the feedback, I'm on the right track. Believe me, I take nothing as an insult, it's all constructive! I can see by reading the article back how I could have explained the extra complexity and made the whole thing easier to read.

    I have another article being published on Thursday, so do please read that as well and give me any feedback - good or bad!

    Cheers, James

    James
    MCM [@TheSQLPimp]

Viewing 15 posts - 1 through 15 (of 36 total)

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