SQL Server如何从列中删除标识(identity)？

Question

有没有一种简单的方法从SQL Server 2005表中删除标识符？使用Management Studio时，它会生成一个脚本来创建一个不带标识符的镜像表，复制数据，删除表，然后重命名镜像表等。由于此表/列具有许多FK关系，因此此脚本包含5231行。我更愿意运行简单的alter / drop。有什么想法吗？编辑：我认为我将使用Enterprise Manager中的5231行脚本。但是，我将将其分解成我可以更好地运行和控制的较小部分。如果尝试删除1行（即使是刚刚插入的行，不在任何其他FK表中），则会出现此错误。

delete MyTable where MyPrimaryKey=1234  

Msg 8621, Level 17, State 2, Line 1
    The query processor ran out of stack space during query optimization. Please simplify the query.

毫无疑问，所有的外键。当我们进行这些模式和相关应用程序更改时，我们将停止所有对应用程序的访问，并在单用户模式下运行。然而，我们需要快速完成此操作，我需要知道需要多长时间。我想我只能进行测试、测试和测试。

- mike

Answer 1

如果您使用的是SQL Server 2005或更高版本，则可以将其作为简单的元数据更改来完成此操作（注意：不需要支持分区的版本，这是我最初声明的）。

示例代码无耻地从此 Microsoft Connect 项目中Paul White的解决方法中窃取。

USE tempdb;
GO
-- A table with an identity column
CREATE TABLE dbo.Source 
(row_id INTEGER IDENTITY PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);
GO
-- Some sample data
INSERT dbo.Source (data)
VALUES (CONVERT(SQL_VARIANT, 4)),
        (CONVERT(SQL_VARIANT, 'X')),
        (CONVERT(SQL_VARIANT, {d '2009-11-07'})),
        (CONVERT(SQL_VARIANT, N'áéíóú'));
GO
-- Remove the identity property
BEGIN TRY;
    -- All or nothing
    BEGIN TRANSACTION;

    -- A table with the same structure as the one with the identity column,
    -- but without the identity property
    CREATE TABLE dbo.Destination 
    (row_id INTEGER PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);

    -- Metadata switch
    ALTER TABLE dbo.Source SWITCH TO dbo.Destination;

    -- Drop the old object, which now contains no data
    DROP TABLE dbo.Source;

    -- Rename the new object to make it look like the old one
    EXECUTE sp_rename N'dbo.Destination', N'Source', 'OBJECT';

    -- Success
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Bugger!
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;
GO

-- Test the the identity property has indeed gone
INSERT dbo.Source (row_id, data)
VALUES (5, CONVERT(SQL_VARIANT, N'This works!'))

SELECT row_id,
        data
FROM    dbo.Source;
GO

-- Tidy up
DROP TABLE dbo.Source;

- Martin Smith

---

找了好久终于找到了！如果可以的话，我会给它加99分 :) - MPritchard

在现实世界的例子中，这可能包括删除对该表的外部表引用。 - George Polevoy

Answer 2

我不认为你可以直接删除列的IDENTITY部分。你最好的选择可能是：

• 向表中添加另一个非identity列
• 将identity值复制到该列中
• 删除原始identity列
• 将新列重命名以替换原始列

如果identity列是键或其他约束的一部分，则需要在完成上述操作后删除这些约束并重新创建它们。

- NYSystemsAnalyst

Answer 3

你可以向表中添加一列，这列不是自增列，然后复制数据，删除原始列，将新列重命名为旧列并重新创建索引。

这里是一个链接（此处），展示了一个例子。虽然不是简单的修改，但肯定比5231行要好。

- Yishai

---

这几乎与5231行管理工作室脚本所做的相同，但由于没有真正的“alter table”方法来执行此操作，因此这似乎是一个不错的选择。 - KM.