Implementing Custom Sequence Numbers

This article will show how to add a row sequencing number to a table that can then be easily maintained and used to order the results by. There are many uses for this but in this article we will use the example of a To-do List. Traditionally, row sequencing numbers like this have been done by the client application retrieving a recordset of the data in question and updating each row with the revised sequencing before passing the whole lot back to SQL Server to save the changes. This works fine but is not the most efficient, especially in a web application. So here is an alternative: logic that will allow SQL Server to do all the work with only the minimal amount of data being passed between the application layers.

So let's start by creating a sample table.

-- 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(todoGroup, 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

As you can see, this table contains a primary key, a pseudo foreign key to a person, and some tasks. You will notice that there is no sequence number as yet, so let's add one (as I suspect anyone who uses this will implement it on an existing table). First we will add a column for the sequence number with a default of zero and then we will populate this. You will notice in the ROW_NUMBER() function that we are partitioning by the Person ID (FK), this is necessary to ensure the sequence start at zero again for each foreign key value (as I doubt anyone will be implementing this on a table with only one foreign key value in!).

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

All good so far: we have a sample table with a sequence number and this is populated. So now onto the fun part; how do we maintain this number without passing a recordset back to the client, having the client maintain the sequence numbers and pass the whole lot back to us to save?

Fear not, for there is an elegant SQL Server solution. We can create a stored procedure that, in one update statement, moves a given row up or down and re-sequences the other rows accordingly.

To do this, we will create a stored procedure with three parameters:

• @personID (our FK)
• @todoID (the PK of the row we're moving)
• @todoSequence (the new zero-based sequence number we want this row to have)

Given these three parameters, the stored procedure can re-sequence all rows for the given FK correctly.

The first thing the stored procedure does is to get the old sequence number for the row in question, so that we can work out if this is moving up or down. Once we have this, we can use one update statement to re-sequence all the rows, using the ROW_NUMBER() function ordered by a custom case statement.

It is the logic in the case statement that does all the work for us and, whilst this may initially look complicated, it is actually rather simple. The case statement logic is as follows:

When the new sequence variable is greater than the old sequence variable:

• if the row's sequence number equals the old sequence number variable then the value is the new sequence number plus 2 (to move it down in the list)
• if the row's sequence number is less than or equal to the new sequence number variable and doesn't equal the old sequence number variable then leave as is (as these rows are higher in the list and already in the correct order)
• if the row's sequence number is greater than the new sequence number variable and doesn't equal the old sequence number variable then the value is the existing sequence number plus 2 (again to move it down in the list

When the new sequence variable is less than then the old sequence variable:

• if the row's sequence number equals the old sequence number variable then the value is the new sequence number plus 1 (to move it up in the list)
• if the row's sequence number is greater than or equal to the new sequence number variable and doesn't equal the old sequence number variable then the value is the existing sequence number plus 2 (again to move it up in the list)
• if the row's sequence number is less than the new sequence number variable and doesn't equal the old sequence number variable then leave as is (as these rows are lower in the list and already in the correct order)

When the sequence variable is the same as the old sequence variable then leave the sequence numbers as they were.

Now I know this may look a little confusing but believe me it works! Why don't we try it. Let's create the stored procedure.

CREATE PROCEDURE [dbo].[setTodoSequence]
(
@personID int,
@todoID as int,
@todoSequence as smallint
)
AS
SET NOCOUNT ON;
declare @oldTodoSequence smallint
-- get the old sequence
set @oldTodoSequence = (select todoSequence from dbo.ToDoList where todoID = @todoID);
begin tran;
BEGIN TRY
 -- resequence
 update dbo.ToDoList set
 todoSequence = ns.newTodoSequence
 from dbo.ToDoList tdl
 inner join
 (
 select todoID,
 todoSequence,
 row_number() over 
 (
 order by
 (case 
 when @todoSequence > @oldtodoSequence then -- moving down as the new sequence number is higher than the old one
 case
 when todoSequence = @oldtodoSequence then @todoSequence + 2
 when (todoSequence <= @todoSequence and todoSequence <> @oldtodoSequence) then todoSequence -- less than new seq so leave alone
 when (todoSequence > @todoSequence and todoSequence <> @oldtodoSequence) then todoSequence + 2
 end
 when @todoSequence < @oldtodoSequence then -- moving up as the new sequence number is less than the old one
 case
 when todoSequence = @oldtodoSequence then @todoSequence + 1
 when (todoSequence >= @todoSequence and todoSequence <> @oldtodoSequence) then todoSequence + 2 -- greater than new seq so leave alone
 when (todoSequence < @todoSequence and todoSequence <> @oldtodoSequence) then todoSequence
 end
 else todoSequence -- not moving (the sequence number is the same as it was before)
 end)
 ) - 1 as newTodoSequence
 from dbo.ToDoList
 where personID = @personID
 ) ns
 on tdl.todoID = ns.todoID; 
 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
 SELECT @@error;
 ROLLBACK TRANSACTION;
END CATCH
GO

With the stored procedure in place, we can now test the functionality. First, let's move the row with the text "Task 8" (ID 9) to sequence number 2 (making it third in the list).

Here is the data before we start:

We now run our stored procedure to make the move.

-- move Line 8 to sequence number 2
exec dbo.setTodoSequence
 @personID = 1,
 @todoID = 9,
 @todoSequence = 2
go

Here is the data now:

As you can see, "Task 8" is now at sequence number two and all other rows have been re-sequenced correctly.

Let's try it again and move "Task 0" (the first task) to sequence 9 (last in the list).

-- move Line 0 to sequence number 9
exec dbo.setTodoSequence
 @personID = 1,
 @todoID = 1,
 @todoSequence = 9
go

Here is the data now:

As you can see, "Task 0" is now at sequence number nine and all other rows have been re-sequenced correctly (notice how "Task 8" which we previously moved to sequence number 2 has been adjusted to now be at sequence number 1 due to "Task 0" moving).

So there you have it, an elegant way of handling custom sequence numbers in your tables. Fire a variation of the above stored procedure off post a re-sequencing in the client application and you're done!

I'm sure I could spend days further enhancing the case statement logic to look better but, as it works and works every time, I'm not touching it!

Enjoy!