-- create the sequence test table create table dbo.ToDoList ( todoID int identity(1,1) not null primary key, todoGroup 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 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 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 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 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!
