主页 / dba / 问题 / 114608
Accepted
Vaccano
Asked: 2015-09-10 10:22:49 +0800 CST

为什么不支持删除列上的 Identity 属性

  • 772

我已经读过，在 SQL Server 2000 之后，删除了“取消标识”标识列的能力。这是“按设计”（不仅仅是缺少的功能）。

这是我在博客上找到的一个例子。它涉及更新系统表。（并且该功能在 SQL Server 2000 之后被删除。）我知道通过系统表执行此操作不是一个好主意。我只是想知道为什么不存在以另一种方式执行此操作的功能。

解决这个问题将导致我做大量的工作。（在不容忍停机的环境中将数亿行复制到新表中。）

所以我想我会问“为什么”。

Sql Server 2005 及更高版本中的哪些变化使这成为一件坏事？还是它总是很糟糕，只是没有被锁定？

将标识列再次设为普通列会违反什么“最佳实践”（或类似原则）？

--

更新以回答“我为什么这样做”的请求：
这是一个非常高级的摘要：我将开始向我的表添加分区。（这样我就可以存档/清除旧数据。）这很容易。但是我偶尔需要将记录移动到不同的分区，这样它就不会被删除（当一个分区出现以进行归档/删除时）。（我的分区列增加了 2，以便始终有空间将行移动到不同的分区。）

但是如果分区列是标识列，那么我必须删除并重新插入该值（没有办法更新标识列的值）。这会导致复制问题。

所以我想使用序列而不是标识列。但是这种切换在大型数据库上非常非常困难。

sql-server identity

1 个回答

  1. Best Answer

    你的问题本质上是：

    为什么我不能再做这种本来就不应该被允许做的冒险事情？

    该问题的答案在很大程度上是无关紧要的（尽管您可以在这些 Connect 项目中看到一些 Microsoft 评论要求此功能：#294193和#252226）。

    为了完整起见，我的概要是：删除身份属性的能力是一个意外的副作用，它首先能够弄乱系统表。这并不打算以多种方式使用，通常会产生非常糟糕的后果，因此它被删除了。

    这是一个未记录的、不受支持的系统表 hack。更改系统表中的数据的功能没有被删除，因为 Microsoft 不再希望您破解作为标识列的列，它被删除是因为篡改系统表非常危险。删除IDENTITY属性本身并不是一个专门针对功能的删除，即使在可能的远古时代，我也永远不会完全相信这种方法。

    也就是说，我们来回答这个问题怎么样？

    如何在最短或没有停机时间的情况下删除列的 IDENTITY 属性？

    你可以很容易地做到这一点，使用ALTER TABLE ... SWITCH我确信我首先从我们自己的Paul White在Connect #252226的解决方法中学到的技术。快速示例，给定这个简单的表格：

    CREATE TABLE dbo.Original
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  name SYSNAME
);
GO

INSERT dbo.Original(name) VALUES(N'foo'),(N'bar');
GO

SELECT * FROM dbo.Original;
GO

    结果：

    ID 姓名
    1 富
    2 酒吧

    现在，让我们创建一个影子表，然后切换到它，然后删除旧表，重命名新表，然后恢复正常活动：

    CREATE TABLE dbo.New
(
  ID INT PRIMARY KEY,
  name SYSNAME
);
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  ALTER TABLE dbo.Original SWITCH TO dbo.New;
  DROP TABLE dbo.Original;
  EXEC sys.sp_rename N'dbo.New', N'Original', 'OBJECT';
COMMIT TRANSACTION;
GO

INSERT dbo.Original(ID,name) VALUES(3,N'splunge');
UPDATE dbo.Original SET ID = 6 WHERE ID = 1;
GO

SELECT * FROM dbo.Original;
GO

    结果：

    ID 姓名
    2 酒吧
    3 挥霍
    6 富

    现在清理：

    DROP TABLE dbo.Original;

    这只是一个元数据操作，没有数据移动，并且只会在元数据更新时阻止其他用户。但是，不可否认，这是一个非常简单的例子。如果您有外键或正在使用复制、更改数据捕获、更改跟踪等其他功能，您可能需要在进行此更改之前禁用或删除其中的一些（我没有测试所有组合）。对于外键，请参阅此提示，该提示显示如何生成脚本以删除和重新创建所有（或选定的）外键约束。

    此外，您将需要更新您的应用程序代码以不期望 SQL Server 填充此列，并检查可能取决于列的顺序或需要指定的列的任何插入或选择语句。一般来说，我会在你的整个代码库中搜索到这个表的任何提及。

    另请参阅Itzik Ben-Gan 的此脚本（来源：这篇古老的文章）以了解另一种处理此问题的方法，但此处涉及数据移动，因此它不满足“无停机时间或停机时间最短”的要求。

    • 22

相关问题