Data Migration Assistant - System-versioned temporal table clustered index duplicate issue

[krisztianharagos]

Hey,

Version 5.5.5310.2 of DMA is sorting the schema extraction the way that first

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sch].[HistTableX]') AND type in (N'U'))
BEGIN
CREATE TABLE [sch].[TableX](
...
	[TenantID] [int] NOT NULL,
	[ID] [bigint] NOT NULL,
	[TransFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[TransTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [sch].[HistTableX] )
)

...

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sch].[HistTableX]') AND type in (N'U'))
BEGIN
CREATE TABLE [sch].[HistTableX](

...

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[sch].[HistTableX]') AND name = N'CIX_HistTableX')
CREATE CLUSTERED INDEX [CIX_HistTableX] ON [sch].[HistTableX]
(
	[TenantID] ASC,
	[ID] ASC,
	[TransFrom] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [S_TenantID]([TenantID])
GO

After the first block executes, the table [sch].[HistTableX] will be created with the clustered index with the period columns ([Transfrom] and [TransTo] in our case).

This will actually create a result message like:

"Cannot create more than one clustered index on table 'sch.HistTableX'. Drop the existing clustered index 'ix_HistTableX' before creating another."

We are relying on the proper clustered index being present on the history tables backing temporal tables as we actively querying those.

Please extract/generate the DDL taking care of this requirement - so the history table creation with it's corresponding clustered index should preceed the creation of the table it's backing.

Thanks