What is the best approach to insert a record between two sequencial rows?

Question

I have a simple sequential table such:

  ID   | Name | Rank
=======+======+=====
  327  | Ali  | 1
-------+------+-----
  846  | Sara | 2
-------+------+-----
  657  | Dani | 3
-------+------+-----
         ...

the ID is primary key and indexed, also the Rank is indexed too. I have couples of these records, and what i want is that insert a record between records of this table in SQL Server, as keeps its sequences without breaking the ranking.

for example i insert Sahar by into the above table with ranking 2, It cause to shifting greater ranks , so :

  ID   | Name | Rank
=======+======+=====
  327  | Ali  | 1
-------+------+-----
  196  | Sahar| 2     ----> Inserted
-------+------+-----
  846  | Sara | 3
-------+------+-----
  657  | Dani | 4
-------+------+-----
         ...

I have searched and i have found some solution to do it for instance :

UPDATE TABLE table SET Rank += 1 WHERE Rank >= 2;
INSERT INTO TABLE (Rank, ...) VALUES (2, ...);

In this answer, or another approach may be this answer. and some other answers i found but all of them have a heavy cost in operation.

Also may I need to change some Ranks or exchange two Ranks so.

In the other hand i have to do it in UPDATE, Delete and Insert Triggers or else-ever you recommend.

1. Is there a mechanism such as identity(1,1) or other built-in service in SQL Server which aims to solve this issue?
2. If NO, what is the best approach in performance aspect of this operation?( the answer should have a good explanation about implementation place (Trigger or ...), Indexing issue and also may be need to change my table definition)

Thanks.

Answer 1

If your table has N rows with Rank from 1 to N, and you insert a new row with Rank=2, then you'll have to UPDATE (i.e. change) values in N-2 rows. You'll have to write a lot of changes to the table. I'm afraid there is no magic way to speed it up.

If you really have to update the Rank, that is.

But, maybe, you don't really need to have the Rank as an integer without gaps.

The real purpose of the Rank is to define a certain order of rows. To define an order you need to know which row comes after each row. So, when a user says that he wants to add Sahar with ranking 2 it really means that Sahar should go after Ali, but before Sara, so the rank of the new rows can be set to, say, (1+2)/2 = 1.5.

So, if you make Rank a float you would be able to insert new rows in the middle of the table without changing values of the Rank of all other rows.

If you want to present Rank to the user as a sequence of integer numbers without gaps use something like:

ROW_NUMBER() OVER(ORDER BY FloatRank) AS IntegerRankWithoutGaps

Besides, if you delete a row, you don't need to update all rows of a table as well. The persisted FloatRank value would have a gap, but it will disappear when ROW_NUMBER is applied.

Technically, you can't keep dividing an 8-byte float interval in half indefinitely, so once in a while you should run a maintenance procedure that "normalizes" your float ranks and updates all rows in a table. But, at least this costly procedure could be run not often and when the load on the system is minimal.

Also, you can start with float values not 1, but further apart. For example, instead of 1, 2, 3, 4... start with 1000, 2000, 3000, 4000, ....