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
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 686
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

James
MCM [@TheSQLPimp]
fahey.jonathan
fahey.jonathan
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 400
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.
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 686
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

James
MCM [@TheSQLPimp]
peter-757102
peter-757102
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 2559
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.
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 686
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

James
MCM [@TheSQLPimp]
Goldie Lesser
Goldie Lesser
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 1501
Interesting article. However, the code in the article is basically re-numbering manually instead of taking advantage of the built in Row_Number() functionality.

This seems to be a lot simpler:
(Note: I changed the table to a temp table for easier testing)


-- create the sequence test table

create table #ToDoList
(
todoID int identity(1,1) not null primary key,
PersonID int not null,
todoText nvarchar(200) not null,
todoSequence smallint not null default(0)
)
go

-- populate the sequence test table
insert into #ToDoList(PersonID, todoText, todoSequence)
select 1, 'Task 0', 0 union all
select 1, 'Task 1', 1 union all
select 1, 'Task 2', 2 union all
select 1, 'Task 3', 3 union all
select 1, 'Task 4', 4 union all
select 1, 'Task 5', 5 union all
select 1, 'Task 6', 6 union all
select 1, 'Task 7', 7 union all
select 1, 'Task 8', 8 union all
select 1, 'Task 9', 9
go

SELECT *
FROM #ToDoList

-- resequence tasks
DECLARE @PersonID BIGINT = 1
, @ToDoID INT = 1
, @NewSeq INT = 9

-- figure out the direction we are moving in
-- down = when the new sequence variable is greater than the old sequence variable
-- up = when the new sequence variable is less than then the old sequence variable
DECLARE @Down BIT -- 1 means down, 0 means up

SELECT @Down = CASE WHEN todoSequence < @NewSeq -- move down
THEN 1
ELSE 0 -- move up
END
FROM #ToDoList
WHERE todoID = @todoID


;WITH NewSequences AS
(
SELECT *
, ROW_NUMBER() OVER(ORDER BY
CASE WHEN TDL.toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END
-- order by new sequence without ordering, two numbers will have the same seq
, CASE WHEN TDL.toDoID = @ToDoID THEN @Down ELSE ~@Down END)
-- pick with of the two will be first, based on wether we are moving up/down
- 1 -- for 0 based list
AS RowNum
FROM #ToDoList TDL
WHERE PersonID = @PersonID
)
UPDATE TDL
SET TDL.todoSequence = NS.RowNum
FROM #ToDoList TDL
JOIN NewSequences NS ON TDL.ToDoID = NS.ToDoID


SELECT *
FROM #ToDoList
ORDER BY todoSequence


-- clean up
DROP TABLE #ToDoList


f7_cisco
f7_cisco
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 8
good article; just a quick note on the insert statement; SQL 2008 allows multiple values in one INSERT statement and is quite faster than an "INSERT... SELECT... UNION ALL":

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )...
grega.jesih 9517
grega.jesih 9517
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 55
Well, I was not as satisfied with rownumber over order-by ..
I needed to enumerate 22mio records and over 3h didn't know wheather I'm on half way or no-way.
So I used an alternative:
1. original table: T(n int, data char..) with 22mio records
2. copy top 0 into T' from T
3. alter table T': make n as autonumber
4. insert into T' from T
if 4th step fails because of transaction lenght, use some criteria on T to only get
part of data, i.e. year(date), etc.

Worked fine.

Grega
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