SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Custom Sequence Numbering


Custom Sequence Numbering

Author
Message
James A Skipwith
James A Skipwith
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 723
Comments posted to this topic are about the item Custom Sequence Numbering

James
MCM [@TheSQLPimp]
Vipul Tyagi
Vipul Tyagi
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 18
Hi, It was a nice article. but I believe that personID column is missing in the table definition. Sorry, if I am wrong.
BarneyL
BarneyL
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 985
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.
James A Skipwith
James A Skipwith
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 723
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
(
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
GO

James
MCM [@TheSQLPimp]
ekoner
ekoner
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 672
Great article. It got me thinking, which first thing on a Monday morning is usually impossible! :-D

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
James A Skipwith
James A Skipwith
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 723
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]
ekoner
ekoner
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 672
Definitely needs more testing, thanks. It will give me something to think about today Smile

Measure twice, cut once
cedarhillr
cedarhillr
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 3
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.
James A Skipwith
James A Skipwith
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 723
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]
James Goodwin
James Goodwin
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 Visits: 1107
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search