主页 / dba / 问题 / 267570
Accepted
Anton1151
Asked: 2020-05-21 18:11:58 +0800 CST

您如何最大限度地减少临时表上系统命名约束之间发生冲突的机会？

  • 772

当我们的一些程序尝试使用系统命名的 PK 约束创建临时表时，我们偶尔会遇到错误“数据库中已经有一个名为 'PK__#TempTab__0796211ACE71813B' 的对象”，如下所示：

CREATE OR ALTER PROCEDURE dbo.StoredProc AS
BEGIN
    CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id))
    ...
END

查询 tempdb.sys.objects 会发现数百个 PK__#TempTab__XYZ 形式的约束。许多是几小时前创建的，从那以后就没有被修改过。当我查看 tempdb.sys.objects 时，活动会话非常少，因此很难相信当前有这么多临时表正在使用。我们确实在许多存储过程中创建了许多类似名称的临时表。

我认为临时表缓存在这里负责。这些 PK 约束似乎一直存在于 tempdb 中，直到它们相关的缓存计划被删除。测试表明，禁用临时表缓存（创建统计信息、添加命名约束等）会导致 PK 约束在临时表超出范围时从 tempdb.sys.objects 中删除。触发重新编译和刷新 proc 缓存也会从 tempdb.sys.objects 中清除这些 PK 约束。

我知道系统命名的约束不能保证是唯一的，如本文所述SQL Server 可以在系统生成的约束名称中创建冲突吗？.

我的问题是：

  • 我是否错误地认为临时表缓存会增加临时表上系统命名约束的名称冲突的机会？
  • 计划缓存膨胀是否会导致我在 tempdb.sys.objects 中看到的大量 PK__#TempTab__XYZ 约束，从而增加两个约束具有相同名称的机会？我试图理解为什么我们经常看到这种罕见的错误。
  • 如果我们不能确保临时表上的系统命名约束永远不会遇到命名冲突，我们可以做些什么来减少这些冲突的机会呢？
sql-server-2016 tempdb

1 个回答

  1. Best Answer

    最简单的解决方案是使用唯一的聚集索引，它在系统中不必是唯一的（与主键约束不同）。否则，它们的功能大多相同——唯一索引仍然是防止重复的约束，也可以用作外键的目标。

    我忘记了这种特定的内联索引语法在 2016 年是否可行（我在本地只有 2019 年），但这在较新的版本上运行良好：

    CREATE TABLE #fooA(id int, INDEX PK1 UNIQUE clustered(id));
CREATE TABLE #fooB(id int, INDEX PK1 UNIQUE clustered(id));

    如果该语法在 2016 年还无效，您可以在第二步中创建一个唯一的聚集索引，您必须自己命名（但您不必找到一些复杂的方法来使该名称唯一）。

    此外，不要过度使用通用名称，如#TempTable，使用更特定于表内容/过程功能的名称（如#Orders、#Customers等）。在您需要显式 PK 的情况下，如果您的所有PK 不再是 system-named ，这至少会减少发生冲突的机会PK__#TempTab_...。

    顺便说一句，我认为我从未在系统中见过如此多的临时表，以至于在这些长的、试图成为唯一的名称上发生冲突。您是否试图淹没元数据？

    • 1

相关问题