TSQL Set Based Sequencing

Consider you have a database table with a sequence field (you might also call it your order field) and you need a fast way to rearrange the items without using a database cursor or some nasty recursive loop.  Fortunately, there is a set based solution.

For example, let us consider the following table:

[Photos]
PhotoID int identity(1, 1) not null,
AlbumID int not null,
SequenceNo int not null
Filename varchar(200) not null,
Caption varchar(50) null

Now, we will assume the following rules:

• Each album has it's own set of sequence numbers for the photos contained in the album.
• SequenceNo for new Photos is max(SequenceNo) + 1 where the AlbumID is equal to the AlbumID of the new Photo 

If you wish to move a photo either up or down in position (i.e. increment or decrement the SequenceNo), the following TSQL is the fastest approach to accomplishing this...

-- Hardcoded values for this example 

set @PhotoID = 1234
-- The positive or negative change in sequence to be applied
set @SequenceDelta = -1 

-- First, we need to obtain the AlbumID associated with this photo
declare @AlbumID int
select @AlbumID = AlbumID from Photo where PhotoID = @PhotoID

-- Next, we need the current sequence for this photo
declare @CurrSeq int
select @CurrSeq = SequencNo from Photo where PhotoID = @PhotoID

-- Next, we must make room for the move
if (@SequenceDelta > 0) begin
    -- This is an increase in position (sequence)
    -- So we shift the items >= the new position up
    update Photos set SequenceNo = SequenceNo - 1
    where SequenceNo <= (@CurrSeq + @SequenceDelta) and 
        AlbumID = @AlbumID
end

if (@SequenceDelta < 0) begin
    -- This is a decrease in position (sequence)
    -- So we shift the items <= the new position down
    update Photos set SequenceNo = SequenceNo + 1
    where SequenceNo >= (@CurrSeq + @SequenceDelta) and 
        AlbumID = @AlbumID
end

-- Next, we update the photo record to it's new position
update Photos set SequenceNo = SequenceNo + @SequenceNo
where PhotoID = @PhotoID

-- Finally, we used set based sequencing to resequence the photos in the album
-- so that the sequence numbers are in contiguous order starting at number 1
update Photos set SequenceNo = (
    select count(*) + 1 from Photos p
    where (
        (p.SequenceNo < Photos.SequenceNo) or
        (
            (p.SequenceNo <= Photos.SequenceNo) and
            (p.Caption < Photos.Caption)
        )
    ) and
    (p.AlbumID = Photos.AlbumID)
)
where AlbumID = @AlbumID

If you just had a Photos table with no AlbumID (i.e. no subsets of records) and you just wanted to keep all the records in a contiguous order, it is as simple as removing the 2 where clauses comparing the AlbumIDs.


Note: the where clause (p.Cation < Photos.Caption)  assures us that if 2 or more Photos in the same album happen to have the same SequenceNo, their sequence numbers will be determined by the value of their Caption.  More clauses could be added to further account for similarities.  For example if 2 or more photos had the same sequence number and caption, the filename could also be compared.  A condition where this might happen would be if a photo was moved to a different Album.