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 2 of 4

««1 2 3 4 »»»

Custom Sequence Numbering

Rate Topic

Display Mode

Topic Options

Author

Message

CELKO

Posted Monday, February 07, 2011 9:17 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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_SEQ
ON Task_List.task_id = N_SEQ.task_id
WHEN MATCHED
THEN UPDATE
SET task_seq = N_SEQ.new_task_seq;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1059646

James A Skipwith

Posted Monday, February 07, 2011 9:29 AM

Valued Member

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

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]

Post #1059670

James A Skipwith

Posted Monday, February 07, 2011 9:30 AM

Valued Member

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

Post #1059674

James A Skipwith

Posted Monday, February 07, 2011 9:40 AM

Valued Member

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

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]

Post #1059687

jdcook72

Posted Monday, February 07, 2011 10:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:34 PM
Points: 2, Visits: 54

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.

Post #1059760

James A Skipwith

Posted Monday, February 07, 2011 12:18 PM

Valued Member

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

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]

Post #1059836

James Goodwin

Posted Monday, February 07, 2011 2:27 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 6:10 AM
Points: 1,322, Visits: 1,070

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

Post #1059912

James A Skipwith

Posted Monday, February 07, 2011 2:57 PM

Valued Member

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

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]

Post #1059934

mkromer

Posted Monday, February 07, 2011 11:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 7:28 AM
Points: 3, 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 ...

Post #1060030

James A Skipwith

Posted Tuesday, February 08, 2011 2:15 AM

Valued Member

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

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]

Post #1060080

« Prev Topic | Next Topic »

35 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions