Shifting records in SQL Database while sorting with algorithm I have a table in a SQL Server database, which holds information of some images, and the relevant gallery of them. The columns are like:

ImageId, GalleryId, Order

I have a unique key on GalleryId-Order columns, that is, each image should have a unique order in its gallery.

A business requirement is to change the place (order) of an image, inside the gallery. For example, image at the order of 16, might be replaced at the new order 7. However, all the images should shift, if necessary, to provide space for this sorting operation. In this example, images with the order 7, 8, 9, ..., 15 should shift forward one level, so that the place 7 would become empty for the mentioned image.

I've written a procedure for this:

create procedure MoveImage(@imageId int, @newOrder int)
as
begin
    -- Finding the related gallery
    declare @galleryId int;
    select @galleryId = GalleryId
    from Images
    where ImageId = @imageId 

    -- Finding the count of images inside this gallery
    declare @mediaCount int;
    select @mediaCount = COUNT(ImageId)
    from Images
    where GalleryId = @galleryId

    -- Finding the current order of the image
    declare @currentOrder int;
    select @currentOrder = [Order]
    from Images
    where ImageId = @imageId

    -- Temporary shifting the current media to one order above all, to bypass the 'Each Media Should Have a Unique Order in its Gallery' unique index
    update Images
    set [Order] = @mediaCount + 1
    where ImageId = @imageId

    -- Shifting all media of the gallery between the current order and the new order, one order forward, or backward
    if @newOrder &lt; @currentOrder
    begin
        update Images  
        set [Order] = [Order] + 1
        where GalleryId = @galleryId and [Order] &gt;= @newOrder and [Order] &lt;= @currentOrder
    end
    else
    begin
        update Images  
        set [Order] = [Order] - 1
        where GalleryId = @galleryId and [Order] &lt;= @newOrder and [Order] &gt;= @currentOrder
    end

    -- Inserting the image at the specified order
    print @newOrder
    update Images
    set [Order] = @newOrder
    where ImageId = @imageId
end
go


It works, just fine (at least as much as I've tested it). However, I don't feel Okay with this procedure, I think it might become easier and more effective. Can anyone improve this T-SQL code?

PS: parameters are checked before being passed to this procedure, so presume that there is nothing wrong with parameters, like negative @newOrder, etc.
There is a much, much, much easier way to do this. You can use real values for order, so when you want to move the row from position 16 to position 7 you give it an order value which is the average between the order values of rows 6 and 7.

The precision of real numbers is huge, and I presume that the re-orderings of images in your database will be happening at human reaction speeds, so you probably won't run into precision problems during the remainder of the expected lifetime of the universe. But if you are really insecure about the possibility of precision problems, you can rectify your table every once in a while, by reading the rows sorted by their order, and writing them into a new table, re-assigning the order values by copying them from an integer identity field. Then you delete the old table and rename the new table to the old name.