SQL Server 如何从列中删除标识 [英] SQL Server how to drop identity from a column

问题描述

是否有一种简单的方法可以从 SQL Server 2005 中的表中删除标识?

Is there an easy way to remove an identity from a table in SQL Server 2005?

当我使用Management Studio时，它会生成一个脚本，创建一个没有身份的镜像表，复制数据，删除表，然后重命名镜像表等.这个脚本有5231行，因为这个表/列有很多FK关系.

When I use Management Studio, it generates a script that creates a mirror table without the identity, copies the data, drops the table, then renames the mirror table, etc. This script has 5231 lines in it because this table/column have many FK relations.

我会觉得运行简单的更改/删除更舒服.有什么想法吗?

I'd feel much more comfortable running a simple alter/drop. Any ideas?

编辑
我想我将使用 Enterprise Manager 中的 5,231 行脚本.但是，我将把它分解成更小的部分，以便我可以更好地运行和控制.此表行为"很奇怪，如果您尝试删除 1 行(即使是您刚刚插入的行，不在任何其他 FK 表中)，您会收到此错误:

EDIT
I think I'm just going to go with the 5,231 line script from Enterprise Manager. However, I'm going to break it up into smaller parts which I can run and control better. This table "behaves" strange, if you try to delete 1 row (even one you just inserted, which is not in any other FK table), you get this error:

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.

毫无疑问，所有的 FK.当我们进行这些架构和相关应用程序更改时，我们将停止对我们应用程序的所有访问并以单用户模式运行.但是，我们需要它快速运行，我需要知道需要多长时间.我想我只需要测试，测试，再测试.

No doubt, all the FKs. We will halt all access to our application and run in single user mode when we make these schema and related application changes. However, we need this to run fast, and I need an idea of how long it will take. I guess that I'll just have to test, test, test.

推荐答案

如果您使用的是 SQL Server 2005 或更高版本，您可以将其作为简单的元数据更改来执行(注意:不需要正如我最初所说的那样支持分区的版本).

If you are on SQL Server 2005 or later, you can do this as a simple metadata change (NB: doesn't require an edition supporting partitioning as I originally stated).

示例代码从 Paul White 在 此 Microsoft Connect 项.

Example code pilfered shamelessly from the workaround by Paul White on this Microsoft Connect Item.

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;

这篇关于SQL Server 如何从列中删除标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！