SQL Server 2008 - Adding a column and a filtered index within a single transaction?

Question

This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two transactions where you manually cancel the changes in made in the first one, should it return an error.

But all the same, I'm interested in the technical reasoning behind this phenomena, especially considering I've done this before. Have I activated some interesting overzealous checking on SSMS? Example code below, and thanks!

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

Answer 1

You can do it within the same transaction it just needs to be pushed down into a child scope so compiled separately.

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;

You can also do it within the same scope and in the same transaction if the table does not yet exist.

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;

In this second case the reference to the column is subject to deferred compilation as the table does not yet exist when SQL Server compiles the batch. See Deferred Name Resolution and Compilation in BOL

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.