February 6, 2011 at 10:30 pm
Comments posted to this topic are about the item Custom Sequence Numbering
James
MCM [@TheSQLPimp]
February 7, 2011 at 1:25 am
Hi, It was a nice article. but I believe that personID column is missing in the table definition. Sorry, if I am wrong.
February 7, 2011 at 1:27 am
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.
February 7, 2011 at 1:32 am
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]
February 7, 2011 at 2:26 am
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
February 7, 2011 at 2:34 am
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]
February 7, 2011 at 2:36 am
Definitely needs more testing, thanks. It will give me something to think about today 🙂
Measure twice, cut once
February 7, 2011 at 5:42 am
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.
February 7, 2011 at 6:28 am
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]
February 7, 2011 at 8:50 am
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
February 7, 2011 at 9:29 am
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]
February 7, 2011 at 9:30 am
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]
February 7, 2011 at 9:40 am
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]
February 7, 2011 at 10:46 am
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.
February 7, 2011 at 12:19 pm
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