Is there a way to add an Index in a Fabric data warehouse?

[arpost]

Greetings, community. I am wanting to add some SQL indexes (or the Fabric DW equivalent) to some tables in a data warehouse to boost performance. However, I get the following error when I try to run a CREATE INDEX statement:

CREATE INDEX is not a supported statement type.

Does Fabric DW not have a way to index tables?? Or is the correct way to index tables via some other option?

[KevinChant]

Indexes are not supported, but other features like statistics are. Which particular problem are you trying to solve?

[arpost]

@AndyDDC and @KevinChant, thanks for the replies. Regarding what problem I'm trying to solve, I was trying to boost performance on a query that is joining a data table and a date table that seemed to be running more slowly than I'd like. I thought adding an index or two could help boost join performance, especially since the data had been recently inserted for those two tables.

Given that indexes aren't a thing with Fabric/Parquet, is there an alternative approach that I should take such as updating statistics? There could be cases where there are certain combos of columns that are typically used in conjunction with each other, which could merit a traditional index.

[Anonymous]

Hi @arpost ,

Thanks for the reply from @NandanHegde / @AndyDDC .

The CREATE INDEX statement is not supported in Fabric Data Warehouse.

Related information can be viewed here:

Tables in data warehousing - Microsoft Fabric | Microsoft Learn

As an alternative, similar indexing functionality can be implemented using primary keys, foreign keys, and unique constraints.

Related information can be viewed here:

Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AndyDDC]

Hi @arpost Indexing is not supported within the Warehouse service.  This is a different engine and storage, based on delta and parquet.  The parquet files themselves are not only the data and stats, but also the index itself.  Parquet files are columnar oriented which provides a "columnstore index".  So no support to traditional indexing like clustered/nonclustered.

[MartinFM]

Which would imply that performance in the Warehouse in Fabric will never depend on indexes as we are traditionelly used to from SQL Server but will be only dependent on the underlying parquet files and the Polaris engine. So there is nothing we can do if performance is low?

[frithjof_v]

Some related links from MS Learn docs and Reddit Microsoft Fabric:

https://learn.microsoft.com/en-us/fabric/data-warehouse/guidelines-warehouse-performance

https://www.reddit.com/r/MicrosoftFabric/s/WXa49jpHZx

Hopefully something in the links can help

[NandanHegde]

Based on my understanding, Indexing is not yet supported in Fabric warehouse