Set Order of top 10 Product manually in database

I am creating a database for a mall. Client wants to set top 10 brands or stores manually. He wants to set order manually any time, for example:

store_Name     priority
Puma             1
Reebok           2
Dominos          3
Nike             4
NumeroUno        5

Product will be shows to user ordered by priority. Priority 1 will be shown at top.

My question is when I want to change the priority of store names like I want Nike to be shown at the top of search.

When I set the priority of Nike to 1 then automatically existing 1 position becomes 2 and 2 position becomes 3 and so on. How do I do this mechanism in SQL Server (stored procedure or Trigger)?

I am using SQL Server 2014 and my table has thousands of stores. I want 10 selectively store which is shown at the top of mobile app.

Just for fun you can design a data model for which updating a row to rank N requires you to scan N rows and to update the value for just 1. Displaying the top N ranks also requires you to scan N rows. One weakness is that filtering down to just the Nth rank also requires you to scan N rows. Perhaps this could be useful if someone had many rows in their table and didn't want to potentially update millions of rows to perform a single rank change.

The key is to store a separate internal value for the rank that's different than the displayed rank but still in the correct order. Here I will store the internal priority as NUMERIC(10,6). For each update we can find the previous RAW_PRIORITY value and subtract 0.000001 from it. That will preserve the order with just a single row update. With that precision end users change the ranks of items up to one million times before problems start happening. The precision and scale can be further increased depending on your requirements.

Define the table:

DROP TABLE IF EXISTS dbo.X_STORES;

CREATE TABLE dbo.X_STORES (STORE_NAME VARCHAR(100), RAW_PRIORITY NUMERIC(10,6));

INSERT INTO dbo.X_STORES VALUES
('Puma',1),
('Reebok',2),
('Dominos',3),
('Nike',4),
('NumeroUno',5)

CREATE INDEX X_STORES__PRIORITY ON dbo.X_STORES (RAW_PRIORITY) INCLUDE (STORE_NAME);

Depending on exact requirements it may make sense just to cluster the table on RAW_PRIORITY. We also want a view to make it easy to display ranks in a human readable format as well as to make updates easier:

CREATE OR ALTER VIEW dbo.X_STORES_PRIORITY_ORDER
AS
SELECT STORE_NAME, ROW_NUMBER() OVER (ORDER BY RAW_PRIORITY) [PRIORITY], RAW_PRIORITY
FROM dbo.X_STORES;

With our starting date set Nike is 4th:

SELECT STORE_NAME, [PRIORITY]
FROM dbo.X_STORES_PRIORITY_ORDER
ORDER BY [PRIORITY];

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Reebok     ║        2 ║
║ Dominos    ║        3 ║
║ Nike       ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Let's change Nike to be ranked second:

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Nike';

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Nike       ║        2 ║
║ Reebok     ║        3 ║
║ Dominos    ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Now let's change Dominos to be first:

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 0 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Dominos';

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Dominos    ║        1 ║
║ Puma       ║        2 ║
║ Nike       ║        3 ║
║ Reebok     ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

StackExchange Database Administrators Q#148732, answer score: 5