Can't stop execution of "ADD PERIOD"

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

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

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

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

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

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

StackExchange Database Administrators Q#226186, answer score: 6