SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by James Skipwith / Custom Sequence Numbering / Latest Posts

RE: Custom Sequence Numbering

James A Skipwith — Thu, 10 Feb 2011 09:13:15 GMT

Interesting Peter but you still then have the overhead of maintaining the base table upon insert, update and delete. I think you'd have to test with both scenarios to ascertain the impact.Nice to know the article is making people think!Cheers, James

RE: Custom Sequence Numbering

peter-757102 — Thu, 10 Feb 2011 08:30:47 GMT

The main idea of keeping everything in sequence is ofcourse always a performance issue as changing one record implicitly means an update to all following records, which is implicitly expensive and more so for larger tables.To minimize the cost, I see two options:* Keep the sequence numbers in a separate table with a 1 to 1 relationship to the main table. So there is a second table with the same PK as for the original table, and it has one extra attribute, namely a sequence counter with a unique index on it.* Keep a seprarate numbers with table the same number of records as the main table and let it have a foreign key to the main table. The primary key is the sequence number and it also has a unique index on the foreign key in this instance. You never have gaps this way and never delete records other then the last record(s) in the clustered index. Order changes affect only updates to the foreign key on existing pages. The static nature of the last method should work particulary well combined with with view- or table-partitions.I can think of more complex schemes that reduce things ever further by eliminating a lot of updates, and by localizing them. This is best pictured as working with offsets relative to a reference value. By updating the reference value(s), all following records are renumbered automatically without update. To reference the effective sequence number or perform sorts, you reference both the reference and the offeset to get the final sequence number/ordering. In such a scheme, moving a record to the top of the list only requires updating one or more reference records, and sometimes some of the offsets as well. But never all the offset records at once that exist for every records in the main table.

RE: Custom Sequence Numbering

James A Skipwith — Wed, 09 Feb 2011 12:39:52 GMT

Hi Jonathan,Unfortunately the two systems that this is implemented in are seriously high volume OLTP systems and so the extra cost of defensive programming has been vetoed - plus we have no junior developers! I do take your point though and I would probably go for a option 3 (a new option similar to your option 1 but one that doesn't require an additional lookup): this would be to check the @@ROWCOUNT after the update and raise an error if it was zero - this would tell the caller that there had been some kind of issue without incurring too much extra overhead. Tables with sequences like this are generally very active, so the least time taken up with sequencing the better - hence the complicated "all in one hit" update statement!This has given me food for thought though, as in future articles I will now include some defensive programming, with a comment that if performance is paramount then this can be removed.Thanks again for taking the time to comment on this.Cheers, James

RE: Custom Sequence Numbering

fahey.jonathan — Wed, 09 Feb 2011 11:29:19 GMT

I agree that the UI layer should be passing the correct combination, and there is a problem if it is not. From a defensive programming standpoint, you have created a procedure that will not operate correctly if bad parameters are passed, which leaves the system open to problems. I suggest you have two choices.Option 1 is to exclude the PersonID from the parameters. The procedure doesn't need the UI to pass it because the value can be looked up from the ToDoID. Without the parameter, there is no possibility that the UI will pass the incorrect value. You have eliminated a possible source of error.Option 2 is to validate the PersonID against the value in the ToDoList table. If the UI passes the incorrect value for some reason, you want the database to pass that error back to the UI so the developer knows that incorrect values were passed and can fix the UI. A junior programmer may have made a mistake, but will never see it because he never gets feedback that the mistake was made. On top of that, the procedure will either not perform the intended action or will perform an incorrect action based on bad parameters.I would choose Option 1, which eliminates a source of error and eliminiates the need to validate a parameter.

RE: Custom Sequence Numbering

James A Skipwith — Wed, 09 Feb 2011 02:10:41 GMT

Thank you. It was a great learning experience for me in terms of writing style and content. All the feedback was beneficial and my future articles will surely improve as a result!Cheers, James

RE: Custom Sequence Numbering

SQLRNNR — Tue, 08 Feb 2011 15:50:12 GMT

thanks for the article.

RE: Custom Sequence Numbering

James A Skipwith — Tue, 08 Feb 2011 15:23:37 GMT

Apolgies, didn't answer the todoid and personid question. I'd expect the business/application layer to do this, as if the application was passing me a todoid for the wrong personid then I'd be very concerned. Some business logic in the database layer is good but validation such as this should be done at a higher level. Same goes for "this is already the first one" etc.Cheers, James

RE: Custom Sequence Numbering

James A Skipwith — Tue, 08 Feb 2011 15:10:44 GMT

Again, this is because my procedure also removes gaps in the sequence, hence why it updates all the records. If you try my procedure with different parameters and look at the cache hits it will always hit, whereas the other one will recompile for different parameters combinations. As I stated in an earlier post, I will remove the resequencing to remove gaps and restate at zero into the delete routine, enabling my procedure to also work on a range and drop drastically in complexity. Apologies if you have not read back this far. My procedure suffered by being to all-encapsulating, as if you had a broken sequence due to deletes of say 1,5,7,11,13, mine would set this back to 0,1,2,3,4, whereas the others wouldn't - hence why it is complex and hence why it updates all records.Cheers, james

RE: Custom Sequence Numbering

fahey.jonathan — Tue, 08 Feb 2011 14:55:19 GMT

The original code updates every record for the person, irrespective of whether it changes or not. I think updating only the rows that change makes up for any inefficiency with an "IF" statement.I put the two procedures in the database and looked at the execution plans. It is not always an accurate reflection of execution time, I know, but it does provide very useful information. The original procedure was expected to take 66% of the time and the revised procedure was expected to take 34%. The compiler seems to think the "IF" statements are not a significant detriment.I have one other thing to note. The original procedure accepts as parameters both the PersonID and the ToDoID. The PersonID is dependent on the ToDoID, so either the procedure should not accept the PersonID as a parameter and just look it up, or it should validate that the PersonID passed was the correct PersonID for the ToDoID.

RE: Custom Sequence Numbering

James A Skipwith — Tue, 08 Feb 2011 14:06:33 GMT

Unfortunately the funky code is very efficient. The problem with stored procedures with a lot of IF statements in is that they do not cache well, as the compiler stores a different plan each time a new path is followed, making for poor reuse. The code is complicated to make it efficient. Apolgies if it is hard to follow. I will change the typo as soon as I can. Cheers, James

RE: Custom Sequence Numbering

autoexcrement — Tue, 08 Feb 2011 13:21:45 GMT

[quote][b]fahey.jonathan (2/8/2011)[/b][hr]I have used a similar procedure to move records in a sequence....[/quote]Jonathan's code (above) made sense to me. But I couldn't really figure out what was going on in the original article's case statement. :( It makes sense conceptually, but the specifics of the code were too funky for me to parse. Partly this is because I'm new to this stuff, but also partly because the Jonathan's code is so much more nicely formatted and easy to reconcile. I'd humbly request that the article author reformat his code so it's more readable.And also, please note that as of the time of this posting, the incorrect column name ("todoGroup") is still specified in the original article.

RE: Custom Sequence Numbering

James A Skipwith — Tue, 08 Feb 2011 09:59:28 GMT

Thank you Amy and apologies for the initial typo (which has now been corrected). I have learned a lot about the article writing style from the comments and the whole process has been beneficial for me. I have another one published on Thursday showing how to auto-generate stored procedures and I have triple-checked the attached code to make sure it is 100% correct!Again, thanks for taking the time to comment, I really appreciate it.

RE: Custom Sequence Numbering

James A Skipwith — Tue, 08 Feb 2011 09:55:52 GMT

I think I used zero as the starting sequence due to all the C# programming I have been doing lately - and the hardened C# community would surely spit on anyone starting a sequence or array with 1 - the mere thought of it!!! ;-)After reading the forum I do have started to come to the conclusion that my procedure was maybe overkill in the terms of the scope of what it was trying to do - i.e. both moving the record to the desired position and renumbering the whole sequence in the same process. I am now of a mind to do the gap removal and resequencing to zero in the delete procedure and then change the proc to be more like yours and another one that was posted.I don't have as much validation logic in as yours though, as when I last implemented this the front-end code took care of that. Nice proc though, rare nowadays to see such good error checking! Thanks for taking the time to read the article.

RE: Custom Sequence Numbering

Amy.G — Tue, 08 Feb 2011 09:54:47 GMT

James,I really liked this article. I especially appreciated how you spelled out the logic in text. Many articles make you puzzle it out in the code. Thank you.Amy

RE: Custom Sequence Numbering

fahey.jonathan — Tue, 08 Feb 2011 09:38:52 GMT

I have used a similar procedure to move records in a sequence. I won't repeat the table setup, but I need to point out that I used "1" as the starting value rather than "0" because people normally think of the top item as "1". Here is the procedure I use, formatted to work with the table in the sample.The procedure allow the caller to specifiy either a direction ("TOP", "UP", "DOWN", or "BOTTOM") or a specific sequence number (move item x to sequence number 5).[code="sql"]SET ANSI_NULLS ONSET ANSI_PADDING ONIF OBJECTPROPERTY(OBJECT_ID('ToDoItemMoveRecord'), 'IsProcedure') = 1 DROP PROC ToDoItemMoveRecordGO CREATEPROC ToDoItemMoveRecord @ToDoID INTEGER, @Direction VARCHAR(10) = NULL, @NewSortSeq TINYINT = NULLAS-----------------------------------------------------------------------------------------------------------SET NOCOUNT ONDECLARE @ErrNum INTEGER, @ErrDesc VARCHAR(2000), @ErrRows INTEGER, @ErrSeverity INTEGER, @CurrentSeq TINYINT, @MaxSeq TINYINT, @StartSeq TINYINT, @EndSeq TINYINT, @PersonID INTEGERBEGIN TRY --<logic>Verify the parameters.</logic> SET @Direction = UPPER(@Direction) IF @NewSortSeq IS NULL AND @Direction IS NULL RAISERROR('Please specify either a new SortSeq or a Direction to move.', 16, 1) IF @NewSortSeq IS NOT NULL AND @Direction IS NOT NULL RAISERROR('Please specify either a new SortSeq or a Direction to move, but not both.', 16, 1) --<logic>Locate the current values from the table.</logic> SET @PersonID = (SELECT PersonID FROM ToDoList WHERE ToDoID = @ToDoID) SET @CurrentSeq = (SELECT todoSequence FROM ToDoList WHERE ToDoID = @ToDoID) SET @MaxSeq = (SELECT MAX(todoSequence) FROM ToDoList WHERE PersonID = @PersonID) --<logic>If the new sort sequence is provided, determine the direction of the move.</logic> IF @NewSortSeq IS NOT NULL BEGIN -- Specific sort IF @NewSortSeq < 1 BEGIN RAISERROR('The requested sort sequence cannot be less than 1.', 16, 1) END ELSE IF @NewSortSeq > @MaxSeq BEGIN RAISERROR('The requested sort sequence cannot be more than the current maximum in the table.', 16, 1) END ELSE IF @NewSortSeq = 1 BEGIN SET @Direction = 'TOP' SET @StartSeq = @NewSortSeq SET @EndSeq = @CurrentSeq END ELSE IF @NewSortSeq < @CurrentSeq BEGIN SET @Direction = 'UP' SET @StartSeq = @NewSortSeq SET @EndSeq = @CurrentSeq END ELSE IF @NewSortSeq = @MaxSeq BEGIN SET @Direction = 'BOTTOM' SET @StartSeq = @CurrentSeq SET @EndSeq = @NewSortSeq END ELSE IF @NewSortSeq > @CurrentSeq BEGIN SET @Direction = 'DOWN' SET @StartSeq = @CurrentSeq SET @EndSeq = @NewSortSeq END ELSE BEGIN RAISERROR('The necessary move could not be determined.', 16, 1) END END -- Specific sort --<logic>If a move direction is requested, determine the new sort sequence.</logic> ELSE BEGIN -- Direction requested IF @Direction = 'TOP' BEGIN SET @StartSeq = 1 SET @EndSeq = @CurrentSeq SET @NewSortSeq = @StartSeq END ELSE IF @Direction = 'UP' BEGIN SET @StartSeq = @CurrentSeq - 1 SET @EndSeq = @CurrentSeq SET @NewSortSeq = @StartSeq END ELSE IF @Direction = 'DOWN' BEGIN SET @StartSeq = @CurrentSeq SET @EndSeq = @CurrentSeq + 1 SET @NewSortSeq = @EndSeq END ELSE IF @Direction = 'BOTTOM' BEGIN SET @StartSeq = @CurrentSeq SET @EndSeq = @MaxSeq SET @NewSortSeq = @EndSeq END END -- Direction requested --<logic>Check to be sure the requested move can be made; raise an error if it cannot.</logic> IF @NewSortSeq IS NULL BEGIN -- Invalid record RAISERROR('Record not found.', 16, 1) END -- Invalid record IF @CurrentSeq = 1 AND @Direction IN ('UP', 'TOP') BEGIN -- Invalid record RAISERROR('The record is currently at the top and cannot move up.', 16, 1) END -- Invalid record IF @CurrentSeq = @MaxSeq AND @Direction IN ('DOWN', 'BOTTOM') BEGIN -- Invalid record RAISERROR('The record is currently at the bottom and cannot move down.', 16, 1) END -- Invalid record IF @NewSortSeq = @CurrentSeq BEGIN -- Invalid record RAISERROR('The record is currently at the requested position and cannot be moved.', 16, 1) END -- Invalid record/* -- Testing select @Direction AS '@Direction', @NewSortSeq AS '@NewSortSeq', @Currentseq AS '@CurrentSeq', @MaxSeq AS '@MaxSeq', @StartSeq AS '@StartSeq', @EndSeq AS '@EndSeq'*/ --<logic> - Move the necessary records.</logic> UPDATE ToDoList SET todoSequence = CASE WHEN todoSequence = @CurrentSeq THEN @NewSortSeq WHEN @Direction IN ('TOP', 'UP') THEN todoSequence + 1 WHEN @Direction IN ('DOWN', 'BOTTOM') THEN todoSequence - 1 ELSE NULL END -- Null is used so that an error will occur if the three cases above are not valid. WHERE todoSequence BETWEEN @StartSeq AND @EndSeqEND TRYBEGIN CATCH SET @ErrNum = ERROR_NUMBER() -- Needed for return value SET @ErrSeverity = ERROR_SEVERITY() -- Needed to bubble up the same severity SET @ErrDesc = dbo.FormatErrDesc() GOTO NonTranErrorEND CATCHRETURN NonTranError:RAISERROR (@ErrDesc, @ErrSeverity, 1)RETURN @ErrNum -- Return the error numberGOGRANT EXECUTE ON ToDoItemMoveRecord TO PublicGO[/code]

RE: Custom Sequence Numbering

James A Skipwith — Tue, 08 Feb 2011 02:15:14 GMT

Oh I'm looking forward to that! Currently looking into the new datetimeoffset data type in 2008 to replace my "convert to local date via a utc hour and minute offset lookup table" function! Looks to be the way forward if you deal with data from multiple time zones. I feel amother article coming on this once I've looked into the pros and cons - as let's face it, there are always some cons! Ever tried using the xml nodes method with a very large xml blob? Not good, the old prepare doc and openxml method is way faster but you only find these things out from testing.Thanks again for the supportive comments.Cheers, James

RE: Custom Sequence Numbering

mkromer — Mon, 07 Feb 2011 23:04:13 GMT

Cool solution for custom sequences. If you don't have immediate application needs, you could just wait for Denali (SQL 11) to go RTM and use the new Sequence Object natively in SQL Server ...

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 14:57:09 GMT

James,I would agree that maybe the delete routine would be a better place for the zero re-numbering and that this does over-complicate the routine somewhat.James

RE: Custom Sequence Numbering

James Goodwin — Mon, 07 Feb 2011 14:27:17 GMT

[quote]The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. [/quote]James,While it is true that your routine does this, it seems to me more proper to put that aspect of your code into the Delete routine instead of renumbering everything every time you change the priority on one task.--JimFive

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 12:18:59 GMT

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

RE: Custom Sequence Numbering

jdcook72 — Mon, 07 Feb 2011 10:46:34 GMT

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. [b]Please[/b] 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.

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 09:40:47 GMT

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

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 09:30:43 GMT

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

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 09:29:03 GMT

RE: Custom Sequence Numbering

CELKO — Mon, 07 Feb 2011 09:17:02 GMT

This weekend, I found some old floppy disks with old SQL puzzles and columns i had written 10+ years ago. The original version of this problem was based on parking spaces. Given a motorpool with numbered parking spaces, you want to move the automobiles around. CREATE TABLE Motorpool(parking_space INTEGER NOT NULL PRIMARY KEY CHECK (parking_space > 0), vin CHAR(17) NOT NULL);Re-arrange the display order based on the parking_space column: CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER, @new_parking_space INTEGER)AS UPDATE Motorpool SET parking_space = CASE parking_space WHEN @old_parking_space THEN @new_parking_space ELSE parking_space + SIGN(@old_parking_space - @new_pos) END WHERE parking_space BETWEEN @old_parking_space AND @new_parking_space OR parking_space BETWEEN @new_parking_space AND @old_parking_space;When you want to drop a few rows, remember to close the gaps with this:CREATE PROCEDURE CloseMotorpoolGaps()AS UPDATE Motorpool SET parking_space = (SELECT COUNT (M1.parking_space) FROM Motorpool AS M1 WHERE M1.parking_space <= Motorpool.parking_space);Back then, we did not have ROW_NUMBER(), so today, we might use:CREATE PROCEDURE CloseMotorpoolGaps()AS UPDATE Motorpool SET parking_space = (SELECT ROW_NUMBER() OVER(ORDER BY M1.parking_space) FROM Motorpool AS M1);Likewise, we did not have the MERGE statement and many programmers used the proprietary Sybase UPDATE..FROM.. syntax in spite of its problems. Today, we would use ANSI/ISO Standard code for what you posted: MERGE INTO Task_List USING (SELECT ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id, task_id) - 1 AS new_task_seq, task_id, person_id FROM Task_List) AS N_SEQON Task_List.task_id = N_SEQ.task_id WHEN MATCHED THEN UPDATE SET task_seq = N_SEQ.new_task_seq;

RE: Custom Sequence Numbering

James Goodwin — Mon, 07 Feb 2011 08:50:56 GMT

That seems quite a bit more complicated than this:[code]CREATE PROCEDURE [dbo].[setTodoSequence](@personID int,@todoID as int,@todoSequence as smallint)ASSET NOCOUNT ON;declare @oldTodoSequence smallintdeclare @direction smallintdeclare @start smallintdeclare @end smallint-- get the old sequenceset @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 ENDupdate todolist set todosequence = CASE WHEN todoid = @todoID THEN @todoSequence ELSE todosequence - @direction ENDwhere todosequence between @start and @end and personId = @personid[/code]

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 06:28:31 GMT

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.

RE: Custom Sequence Numbering

cedarhillr — Mon, 07 Feb 2011 05:42:09 GMT

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.

RE: Custom Sequence Numbering

ekoner — Mon, 07 Feb 2011 02:36:13 GMT

Definitely needs more testing, thanks. It will give me something to think about today :)

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 02:34:14 GMT

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

RE: Custom Sequence Numbering

ekoner — Mon, 07 Feb 2011 02:26:53 GMT

Great article. It got me thinking, which first thing on a Monday morning is usually impossible! :-DI 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:[code="sql"]-- create the sequence test tablecreate 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 tableinsert into dbo.ToDoList(PersonId, todoText)select 1, 'Task 0' union allselect 1, 'Task 1' union allselect 1, 'Task 2' union allselect 1, 'Task 3' union allselect 1, 'Task 4' union allselect 1, 'Task 5' union allselect 1, 'Task 6' union allselect 1, 'Task 7' union allselect 1, 'Task 8' union allselect 1, 'Task 9'go insert into dbo.ToDoList(PersonId, todoText)select 2, 'Task 0' union allselect 2, 'Task 1' union allselect 2, 'Task 2' union allselect 2, 'Task 3' union allselect 2, 'Task 4' goSELECT * FROM dbo.ToDoListgoalter table dbo.ToDoList add todoSequence smallint not null default(0) GO-- now add the initial sequenceupdate dbo.ToDoList set todoSequence = n_seq.newSequencefrom dbo.ToDoList tdlinner join(select ROW_NUMBER() over (partition by personID order by personID, todoID) - 1 as newSequence, todoID, personIDfrom dbo.ToDoList) n_seq on tdl.todoID = n_seq.todoID AND tdl.personid = n_seq.personid GO SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequenceGO-- move Line 8 to sequence number 2exec dbo.[setTodoSequence] @personID = 1, @todoID = 9, @todoSequence = 2GO SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequenceGOexec [dbo].[setTodoSequence] @personID = 1, @todoID = 1, @todoSequence = 9go SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequenceGODROP TABLE dbo.ToDoList[/code]Appreciate the article and the new tool in my arsenal.

RE: Custom Sequence Numbering

James A Skipwith — Mon, 07 Feb 2011 01:32:58 GMT

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 tablecreate 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 tableinsert into dbo.ToDoList(personID, todoText)select 1, 'Task 0' union allselect 1, 'Task 1' union allselect 1, 'Task 2' union allselect 1, 'Task 3' union allselect 1, 'Task 4' union allselect 1, 'Task 5' union allselect 1, 'Task 6' union allselect 1, 'Task 7' union allselect 1, 'Task 8' union allselect 1, 'Task 9'go-- add a custom sequence number-- add the new columnalter table dbo.ToDoList add todoSequence smallint not null default(0) go-- now add the initial sequenceupdate dbo.ToDoList set todoSequence = n_seq.newSequencefrom dbo.ToDoList tdlinner join(select ROW_NUMBER() over (partition by personID order by personID, todoID) - 1 as newSequence, todoID, personIDfrom dbo.ToDoList) n_seq on tdl.todoID = n_seq.todoIDGO

RE: Custom Sequence Numbering

BarneyL — Mon, 07 Feb 2011 01:27:49 GMT

[quote][b]Vipul Tyagi (2/7/2011)[/b][hr]Hi, It was a nice article. but I believe that [b]personID [/b]column is missing in the table definition. Sorry, if I am wrong.[/quote]I spotted the same.I presume that the field todoGroup (which is never used)declared in the original table should be PersonID instead.

RE: Custom Sequence Numbering

Vipul Tyagi — Mon, 07 Feb 2011 01:25:52 GMT

Hi, It was a nice article. but I believe that [b]personID [/b]column is missing in the table definition. Sorry, if I am wrong.

Custom Sequence Numbering

James A Skipwith — Sun, 06 Feb 2011 22:30:46 GMT

Comments posted to this topic are about the item [B]Custom Sequence Numbering[/B]