4

I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise). However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.

Here's what I've tried to skip execution if the PERIOD already exists on the table. All of these raise the same error:

Msg 13597, Level 16, State 2, Line xx

Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.

  1. Only add the PERIOD if it doesn't currently exist:

    if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
    

    This raised the error.

  2. Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:

    if 1=2
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
    

    But that raised the error as well.

  3. Use SET NOEXEC ON to stop execution of the code:

    set noexec ON
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
    set noexec OFF
    

    Didn't work. The ALTER TABLE still raised the error.

  4. Admit defeat and wrap it in a TRY/CATCH and just ignore the error:

    begin try
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
    end try
    begin catch
    end catch
    

    This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.

Is this a bug in Azure SQL Server? This is running on a Managed Instance. The @@VERSION reports:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2018 21:17:06 Copyright (C) 2018 Microsoft Corporation

UPDATE:

I can use a similar technique to skip adding columns that already exist without any issues:

if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
GO

The above code only executes if the sysjobhistory does not contain the column StartTime. If it already does, the code does nothing and continues executing. The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.

5

You need dynamic SQL for DDL like this.

IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
    DECLARE @sql NVARCHAR(MAX) = N''
    SET @sql += N'
    ALTER TABLE dbo.sysjobhistory
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

    EXEC sys.sp_executesql @sql
| improve this answer | |
  • 1
    @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work. – Martin Smith Jan 2 '19 at 23:41
2

Statement processing happens in several consecutive phases. Fist comes parsing, then binding, then optimization and finally execution.

I suspect your problem originates at the binding phase. Here SQL Server maps tokens from the statement to objects in the database. Since it finds existing objects for the ones it is trying to create it throws and error. Why the development team chose to implement this branch of ALTER TABLE in binding rather than execution only they can say.

The solution, as Erik says, is to use dynamic SQL. This creates a new "inner" batch which is only submitted for processing during the execution phase of the "outer" batch. Hence the checks are sure to pass (absent concurrent changes).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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