CODEWITHSUNDEEP
azure-synapse
TheLastBrainCell
TheLastBrainCell

Reputation: 65

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

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.

Upvotes: 1

Views: 3028

Answers (2)

Himanshu Kumar Sinha
Himanshu Kumar Sinha

Reputation: 1786

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)

Upvotes: 1

Pratik Somaiya
Pratik Somaiya

Reputation: 733

In Synapse you can create ONLY ONE Clustered Index and on one column.

If you need to further improve performance then you need to create a Non-Clustered Index.

In your case the code will be:

CREATE TABLE [myschema].[test] (
ID1 int UNIQUE NOT ENFORCED,
ID2 int UNIQUE NOT ENFORCED,
message varchar(MAX)) WITH (
CLUSTERED INDEX (ID1))              --Only ID1 is used as clustered index

If you want to make queries work faster you can add Non-Clustered Index.

You can refer below links to know more:

  1. https://www.techrepublic.com/article/index-on-multiple-columns-for-sql-performance/
  2. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index

Thanks :)

Upvotes: 0

Related Questions