在SQL Server中删除时消除序列号“空位”

Question

我在SQL Server数据库中有两个表，它们处于多对多关系，TableA中的行代表我业务中的“容器”结构逻辑和表B是可以包含在任何数量的这些容器中的'子'对象。我创建了一个链接表，TableA_X_TableB中，由列：

TableA_PK UNIQUEIDENTIFIER, TableB_PK INT, Sequence INT 

...与用于记录的表A“容器”内表B项序列中的最后一列 - 因为这些事，其实需要成为有序列表。

我的所有CRUD都非常简单，除了这个：当我从列表中间删除一个项目时，我希望SQL Server在与特定TableA相关的序列号中“密封空位”序列。即，如果我有一个特别的TableA_PK相关的六个条目...

TableA_PK       | TableB_PK | Sequence | 
============================================================= 
AD7D5099-A14D-48D4-9860-6578EDF7C006 |  10389 |  0 | 
AD7D5099-A14D-48D4-9860-6578EDF7C006 |  9368 |  1 | 
AD7D5099-A14D-48D4-9860-6578EDF7C006 |  9537 |  2 | 
AD7D5099-A14D-48D4-9860-6578EDF7C006 |  18499 |  3 | 
AD7D5099-A14D-48D4-9860-6578EDF7C006 |  15759 |  4 | 
AD7D5099-A14D-48D4-9860-6578EDF7C006 |  5872 |  5 | 

...然后执行：

DELETE TableA_X_TableB 
WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006' 
AND TableB_PK = 9537 

...我想序列值读取0 1 2 3 4，而不是0 1 3 4 5。

我已经尝试了很多方法。我就不一一列举，但似乎最有可能经过大量失败的实验（并且也失败了，是明确的！）的一些例子：

DECLARE @seq INT 
SET @seq = -1; 
WITH listEntries AS (
SELECT TOP 100 PERCENT * 
FROM TableA_X_TableB 
WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006' 
ORDER BY Sequence ASC) 
UPDATE listEntries 
SET @seq = listEntries.Sequence = @seq + 1 
FROM listEntries; 

这使得在更新，数字正确的序列号列表，好的...但基于内部数据库的顺序，而不是前一个序列号的排序。结果是用户的排序清单在消除项目时变得混乱。

我能想到的一对夫妇的变通办法此的，但

• 这让我感到，就必须有T-SQL中这样做的相当优雅的方式;
• 这个问题出现之前，很可能会再次出现。我宁愿以“正确”的方式来解决它，而不是继续应用相同的精巧黑客。

谢谢！

UPDATE =========================================== ============

一块拼图是这张桌子也有几个索引;一个用于确保TableA_PK和TableB_PK的任意组合都是唯一的，另外还有一些组合可以加速跨越大数据集的一些连接。如果没有索引，我上面的示例代码运行良好，但是我们天真的ORDER BY子句一旦被使用就会被它们覆盖。 （有可能是一个解决方案在这里，但接受一个远更优雅。）

而且，我怀疑，也有一定的优势情况下的总体要求，其中序列缺口是允许的 - 但前提是，只有最终用户这样说。我无法看到哪一个基于ROW_NUMBER（）的直接解决方案能够解决这个问题; @ jpw下面的巧妙和简洁的'配方'不仅解决了原来的问题，而且稍微调整一下也允许在需要的地方进行本地重新排序。

Answer 1

也许采用分割在TableA_PK ROW_NUMBER（）的顺序排列像这样的工作：

UPDATE Table1 SET Sequence = rn 
FROM Table1 
INNER JOIN (
    SELECT 
     [TableA_PK], 
     [TableB_PK] , 
     rn= ROW_NUMBER() OVER (PARTITION BY tablea_pk ORDER BY tablea_pk, sequence) -1 
    FROM Table1 
    WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006' 
) derived ON table1.TableA_PK = derived.TableA_PK and Table1.TableB_PK = derived.TableB_PK 

Sample SQL Fiddle显示和更新