What's the best way to insert a record between two sequential lines?
I have a simple sequential table like:
ID | Name | Rank
=======+======+=====
327 | Ali | 1
-------+------+-----
846 | Sara | 2
-------+------+-----
657 | Dani | 3
-------+------+-----
...
ID
is the primary key and is indexed as well as indexed Rank
. I have pairs of these records, and I want to insert a record between the records of this table SQL Server
, since it keeps its sequences without breaking the ranking.
for example i am inserting Sahar
into the above table with rank 2, this results in large ranks offset, so:
ID | Name | Rank
=======+======+=====
327 | Ali | 1
-------+------+-----
196 | Sahar| 2 ----> Inserted
-------+------+-----
846 | Sara | 3
-------+------+-----
657 | Dani | 4
-------+------+-----
...
I searched and I found some solution, for example:
UPDATE TABLE table SET Rank += 1 WHERE Rank >= 2;
INSERT INTO TABLE (Rank, ...) VALUES (2, ...);
This, or another approach, may be this answer . and some of the other answers I have found, but they all have high operating costs.
Also I need to change some Ranks
or replace two Ranks
like this.
On the other hand, I must do this at UPDATE
, Delete
and Insert
Triggers
or whenever you recommend.
- Is there a mechanism, like
identity(1,1)
another built-in serviceSQL Server
, that addresses this problem? - If not, what is the best approach in terms of performance of this (the answer should have a good explanation of the place of implementation (trigger or ...), indexing issue, and also the table definition needs to be changed)
Thank.
source to share
If your table has N
rows Rank
from 1
to N
, and you are inserting a new row from Rank=2
, then you will need to UPDATE
(i.e. change) the values in the N-2
rows. You will 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 need to upgrade Rank
, that is.
But maybe you don't really need to have Rank
as an integer with no spaces.
The real goal Rank
is to define a certain order of the lines. To determine the order, you need to know which line appears after each line. So when the user says he wants to add Sahar
with a rank 2
, it really means he Sahar
should go after Ali
but before Sara
, so the rank of new lines can be set to, say (1+2)/2 = 1.5
.
So, if you do a Rank
float, you can insert new rows in the middle of the table without changing the values of Rank
all other rows.
If you want to present to the Rank
user as a sequence of integers without spaces, use something like:
ROW_NUMBER() OVER(ORDER BY FloatRank) AS IntegerRankWithoutGaps
Also, if you delete a row, you also don't need to update all the rows in the table. The stored value FloatRank
will have a space, but it will disappear when applied ROW_NUMBER
.
Technically, you cannot continue to split 8 byte byte intervals in half infinitely, so from time to time you must run a maintenance routine that "normalizes" your floating point series and updates all rows in the table. But at least this expensive procedure can be performed infrequently and with minimal system load.
Also, you can start with float values, not 1
but further. For example, 1, 2, 3, 4...
start with 1000, 2000, 3000, 4000, ...
.
source to share