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

Group: General Forum Members
Points: 284 Visits: 687
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

James
MCM [@TheSQLPimp]
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 687
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

James
MCM [@TheSQLPimp]
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 687
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

James
MCM [@TheSQLPimp]
jdcook72
jdcook72
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 59
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. Please 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.
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 687
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

James
MCM [@TheSQLPimp]
James Goodwin
James Goodwin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 1107
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based.

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
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 687
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

James
MCM [@TheSQLPimp]
mkromer
mkromer
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

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

Group: General Forum Members
Points: 284 Visits: 687
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

James
MCM [@TheSQLPimp]
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