Argthtjtr

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.

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

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).