How do I create a unique clustered index in Azure Synapse Analytics?

Question

I'm trying to create a table with a unique clustered index on Azure Synapse Analytics

I've tried

CREATE TABLE [myschema].[test] (
    ID1 int UNIQUE NOT ENFORCED,
    ID2 int UNIQUE NOT ENFORCED,
    message varchar(MAX)
) 
WITH (
    CLUSTERED INDEX (ID1, ID2)
)

and

CREATE TABLE [myschema].[test] (
    ID1 int,
    ID2 int,
    message varchar(MAX),
    CONSTRAINT test_unique UNIQUE(ID1,ID2) NOT ENFORCED
) 
WITH (
    CLUSTERED INDEX (ID1, ID2)
)

I know they're equivalent and both the times I'm getting this error

Error: Cannot use duplicate column names in statistics. Column name 'ID1' listed more than once.

question from:https://stackoverflow.com/questions/66062209/how-do-i-create-a-unique-clustered-index-in-azure-synapse-analytics

Answer 1

we are aware of this issue and working on a resolution . You do have a workaround for this , please try this and it should help.

CREATE TABLE [test1] (
    ID1 int UNIQUE NOT ENFORCED,
    ID2 int UNIQUE NOT ENFORCED,     
    message varchar(MAX)
) 
WITH (
    HEAP
)
GO
CREATE CLUSTERED INDEX isTest ON [Test1](ID1,ID2)