Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Custom Sequence Numbering Expand / Collapse
Author
Message
Posted Sunday, February 6, 2011 10:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
Comments posted to this topic are about the item Custom Sequence Numbering

James
MCM [@TheSQLPimp]
Post #1059319
Posted Monday, February 7, 2011 1:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 31, 2011 2:38 AM
Points: 14, 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.
Post #1059381
Posted Monday, February 7, 2011 1:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:33 AM
Points: 422, Visits: 738
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.
Post #1059383
Posted Monday, February 7, 2011 1:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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]
Post #1059386
Posted Monday, February 7, 2011 2:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 37, Visits: 626
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





Post #1059408
Posted Monday, February 7, 2011 2:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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]
Post #1059411
Posted Monday, February 7, 2011 2:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 37, Visits: 626
Definitely needs more testing, thanks. It will give me something to think about today :)

Measure twice, cut once





Post #1059412
Posted Monday, February 7, 2011 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 10, 2011 7:12 AM
Points: 2, 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.
Post #1059469
Posted Monday, February 7, 2011 6:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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]
Post #1059487
Posted Monday, February 7, 2011 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
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

Post #1059610
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse