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?

[BhaveshPatel]

Hi @arpost 

You don't need to use Indexing in Fabric SQL/Lakehouse. There is a special command you should use.

You should use Delta Lake and use the below query to do Indexing. ( Single table at a time ) ( _delta_log with parquet file)

OPTIMIZE DimDate; 

[Solution_b]

Indexing in Microsoft Fabric data warehouse is not supported because of the arrangement of the parquet file structure which helps with optimisation of the file structure.

Every table in a Fabric data warehouse is automatically created as a clustered columnstore (compressed column-based storage format) table by default.

query performance is achieved through proper table distribution, clustered columnstore and Optimizing query patterns and statistics.

Best practice:

Table Type	Distribution	Index	Notes
Dimension (small)	REPLICATE	Columnstore (default)	Fast joins
Fact (large) (> 2m)	HASH on join key	Columnstore (default)	Parallel joins
Staging (temp)	ROUND_ROBIN	Heap (no index)	Fast bulk loads
Partitioning	Date column	-	Improves query pruning on large tables

Other work arounds:

There are other methods that Microsoft Fabric Data warehouse uses to optimise your workload like the use of Partitioning data which significantly improves performance. See Kenneth A. Omorodion article Partitioning Data in Microsoft Fabric to Improve Performance

1. Keneth explained the work around to include:
2. Petition the file in the Lakehouse – in parquet
3. Partition in delta table and use it for your warehouse
4. Partition during copy job activity from Bronze Lakehouse into a silver Lakehouse

[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