☰

如果我删除聚集 PK 并添加一个新的，我的行将按什么顺序排列？

tag-icon • tag-icon sql-server indexes
如果我删除聚集 PK 并添加一个新的，我的行将按什么顺序排列？

在 SQL Server 中，我正在查看 TableA，它当前具有唯一标识符聚集主键。GUID 在任何情况下都没有意义。

（我给你一点时间清理你的键盘和显示器并放下苏打水。）

我想删除该主键并向表中添加一个新的唯一整数主键。我的问题是：当我删除索引、将列从 uniqueidentifier 修改为 int 并将新的聚集唯一主键添加到修改后的列时，新的 PK 值将按照插入表中的顺序排列，还是按照其他顺序排列？这是正确的做法吗？这样做可行吗？（我对表创建/修改有点菜鸟。）

答案1

当您删除聚集索引时，表将变成堆。由于堆的物理结构与索引非常不同，因此必须将数据复制到新结构中。堆没有任何顺序。当您重新添加新的聚集索引时，数据将从堆复制到新索引中，顺序将由新的聚集键定义。

如果您想保留现有的顺序，那么您所要做的就是正确分配新的整数 ID：

ALTER TABLE Table ADD Integer_Id INT;
GO

WITH cte AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Guid_Id) as RowOrderByGuid,
    Guid_Id
  FROM Table)
UPDATE t
  SET t.Integer_Id = c.RowOrderByGuid
FROM Table t
JOIN cte c ON t.Guid_Id = c.Guid_Id;

现在 Integer_Ids 的顺序将与 Guids 的顺序相匹配。您可以删除 Guid 列并在新的 Integer 列上添加聚集索引，这样记录的物理顺序就会保留下来。

答案2

根据定义，聚集索引对实际数据页施加了物理排序；所以，是的，如果删除聚集索引并创建一个新的聚集索引，这将强制对数据进行物理重新排序。

就您而言，我认为可以安全地假设会发生以下情况：

  • 现有的聚集索引将被删除，但磁盘上的实际数据不会因此移动。
  • 您将修改列类型（或删除现有列并创建一个新列），设置其约束为非空、唯一、主键、标识和自动增量（这很重要，否则 SQL Server 甚至不会允许您添加它，因为它不知道要放入什么）。
  • 此时，SQL Server 将自动填充该列。我不确定这里会发生什么，但我思考它将按照行在数据库中的物理存储顺序进行填充。但我只是对此进行猜测。
  • 问题是，当涉及到 UID 时，排序可能会非常混乱；所以您不知道数据现在是如何存储的，也不知道以后将如何存储；如果我对列数量的猜测是正确的，那么就不会有大规模的重新排序……但它可能会发生；而且，即使我是正确的，如果表足够大，索引构建无论如何也要花费一段时间。

底线：你将要产生巨大的影响，而你可以按照与现在获取行相同的顺序从无序 SELECT 中获取行。您必须尝试一下。

答案3

根据定义，聚集索引决定了数据的物理顺序，因此当您创建新的聚集索引时，数据将被重新排序；如果它是一个大表，计划需要一段时间。

答案4

如果您创建一个具有聚集主键的表，然后删除聚集主键，则表中数据的物理顺序将不会受到干扰。但是，查询结果的物理顺序不能保证与表中的顺序相同，因此这种排序毫无意义。

如果您随后添加一个整数列并在其上创建一个聚类主键，则表将重新排列为键排序的顺序。根据键的分配方式，这可能是也可能不是与 GUID 相同的物理顺序。您可以根据 GUID 键的排序顺序明确分配它（例如，使用 row_number() 覆盖旧键排序），也可以以其他方式分配它。除非您采取措施确保排序明确相同，否则不能保证物理顺序或表中的行能够驱动新键的排序。

相关内容