主页 / dba / 问题 / 31362
Accepted
Kahn
Asked: 2013-01-04 06:54:29 +0800 CST

SQL Server 2008 - 在单个事务中添加列和过滤索引？

  • 772

这是一个更普遍的问题。为什么不能在与最初添加新列相同的事务范围内添加过滤索引？解决方案当然很简单，只需进行两个事务，如果第一个事务返回错误，您可以手动取消对第一个事务所做的更改。

但同样，我对这种现象背后的技术推理很感兴趣，尤其是考虑到我以前做过这件事。我是否在 SSMS 上激活了一些有趣的过度检查？下面的示例代码，谢谢！

--CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN           
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX 
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN   
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;
sql-server-2008 transaction

1 个回答

  1. Best Answer

    您可以在同一事务中执行此操作，只需将其下推到单独编译的子范围即可。

    BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN           
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            EXEC('CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX 
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL')
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN   
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

    如果表尚不存在，您也可以在同一范围内和同一事务中执行此操作。

    DROP TABLE TEST_TABLE

GO

BEGIN TRY
    BEGIN TRAN

        CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN           
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX 
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN   
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

    在第二种情况下，对列的引用会受到延迟编译的影响，因为当 SQL Server 编译批处理时该表尚不存在。请参阅BOL 中的延迟名称解析和编译

    延迟名称解析只能在引用不存在的表对象时使用。创建存储过程时所有其他对象必须存在。例如，当您在存储过程中引用现有表时，您不能为该表列出不存在的列。

    • 4

相关问题