Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Ah, excellent idea, using dynamic SQL for this. Still, why does the problem appear in the first place? –  Kahn Jan 3 '13 at 15:26
1  
@Kahn - Because it tries to compile all statements in the batch before it executes the batch and the column does not exist yet (ALTER TABLE TEST_TABLE ADD NEWCOL_ID INT NULL hasn't been executed yet) so compilation of that statement fails. If the statement references a non existent table then instead of failing due to that it will defer compilation and the statement will be recompiled after the column is added hence why the second one succeeds. –  Martin Smith Jan 3 '13 at 15:28
    
Just read up on deferred name resolution as you replied, and now I got it. Now I also understand why this has worked before, since as you pointed out, the tables themselves where created within the same transaction in my prior scripts. Thanks for the quick reply and great explanation! :) –  Kahn Jan 3 '13 at 15:31
    
No problem. Glad it helped! –  Martin Smith Jan 3 '13 at 15:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.