Custom Sequence Numbering

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James Skipwith » Custom Sequence Numbering

35 posts, Page 4 of 4

«««1 2 3 4

Custom Sequence Numbering

Rate Topic

Display Mode

Topic Options

Author

Message

James A Skipwith

Posted Wednesday, February 09, 2011 2:10 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

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]

Post #1060910

fahey.jonathan

Posted Wednesday, February 09, 2011 11:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 11, 2013 5:58 PM
Points: 100, Visits: 347

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.

Post #1061423

James A Skipwith

Posted Wednesday, February 09, 2011 12:39 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

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]

Post #1061494

peter-757102

Posted Thursday, February 10, 2011 8:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:04 AM
Points: 287, Visits: 1,901

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.

Post #1062054

James A Skipwith

Posted Thursday, February 10, 2011 9:13 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

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]

Post #1062087

« Prev Topic | Next Topic »

35 posts, Page 4 of 4

«««1 2 3 4

Permissions