Início / dba / Perguntas / 31362
Accepted
Kahn
Asked: 2013-01-04 06:54:29 +0800 CST

SQL Server 2008 - Adicionando uma coluna e um índice filtrado em uma única transação?

  • 772

Esta é mais uma questão geral. Por que você não pode adicionar um índice filtrado no mesmo escopo da transação ao adicionar a nova coluna em primeiro lugar? A solução é obviamente simples, basta fazer duas transações onde você cancela manualmente as alterações feitas na primeira, caso ela retorne um erro.

Mesmo assim, estou interessado no raciocínio técnico por trás desse fenômeno, especialmente considerando que já fiz isso antes. Eu ativei alguma verificação superzelosa interessante no SSMS? Exemplo de código abaixo, e obrigado!

--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 respostas

  1. Best Answer

    Você pode fazer isso dentro da mesma transação, só precisa ser empurrado para um escopo filho, compilado separadamente.

    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;

    Você também pode fazer isso dentro do mesmo escopo e na mesma transação se a tabela ainda não existir.

    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;

    Nesse segundo caso, a referência à coluna está sujeita à compilação adiada, pois a tabela ainda não existe quando o SQL Server compila o lote. Consulte Resolução e Compilação de Nomes Adiados em BOL

    A resolução de nome adiada só pode ser usada quando você faz referência a objetos de tabela inexistentes. Todos os outros objetos devem existir no momento em que o procedimento armazenado é criado. Por exemplo, quando você faz referência a uma tabela existente em um procedimento armazenado, não pode listar colunas inexistentes para essa tabela.

    • 4

relate perguntas