topic Re: Is there a way to add an Index in a Fabric data warehouse? in Data Warehouse

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

arpost — Tue, 16 Jul 2024 13:32:46 GMT

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?

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

NandanHegde — Tue, 16 Jul 2024 14:28:30 GMT

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

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

AndyDDC — Tue, 16 Jul 2024 20:08:14 GMT

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.

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

Anonymous — Wed, 17 Jul 2024 01:16:43 GMT

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!

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

KevinChant — Wed, 17 Jul 2024 20:06:52 GMT

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

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

arpost — Mon, 22 Jul 2024 13:46:39 GMT

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.

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

MartinFM — Wed, 20 Nov 2024 09:44:03 GMT

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?

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

frithjof_v — Wed, 20 Nov 2024 13:51:39 GMT

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

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

Solution_b — Tue, 16 Sep 2025 14:16:22 GMT

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

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

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

BhaveshPatel — Sun, 21 Sep 2025 05:01:59 GMT

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;

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

AnmolGan81 — Mon, 27 Oct 2025 08:27:59 GMT

You can build a mechanism for adding incremental numbers (in your stored procedure SCD1/2 Logic) on a table and build a controling tables for all such indexes, so that in case of any insert index of that record in controling table updates to new index.

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

deborshi_nag — Mon, 01 Dec 2025 14:28:34 GMT

Fabric DW uses distributed MPP architecture with hash-distributed tables, replicated tables, and round-robin tables.

There are no traditional indexes, performance depends on distribution strategy and statistics.

Use the following approaches to get an improvement in performance in queries -

1 - Use Correct Table Distribution

2 - Ensure both tables share the same distribution key

3 - Use Join Hints

4 - Apply WHERE filters before joins to reduce data movement

5 - Create materialized views to precompute joins and aggregates

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

praveen_511 — Wed, 24 Dec 2025 04:31:27 GMT

arpost
You cannot use CREATE INDEX. It is a SaaS (Software as a Service) warehouse that manages performance automatically. The Direct Solution Instead of manual indexes, Fabric uses Clustered Columnstore storage and V-Order optimization by default.

To improve performance, use these three tactics:
1. Maintain the Table: If a table has seen many updates or deletes, run this command to defragment and optimize the underlying files: SQL ALTER TABLE [Schema].[TableName] REORGANIZE;

2. Use Partitioning: For tables with billions of rows, partition by a column (like Date) to enable "Partition Elimination," which skips unneeded data blocks.

3. Optimize the Delta Lake: Since Fabric stores data in Delta/Parquet format, it uses Metadata Skipping. Ensure your WHERE clauses use columns with high cardinality (unique values) to help the engine skip irrelevant data.

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

rizalard0684 — Tue, 30 Dec 2025 07:40:04 GMT

arpost here is my TLDR answers for you.

CREATE INDEX isn’t supported in Microsoft Fabric Data Warehouse. Fabric DW relies on columnar (Parquet Delta) storage plus statistics instead of traditionally user‑managed B‑tree indexes.

Some tactics to improve query performance:

1. Create & refresh statistics on join/filter columns

CREATE STATISTICS ColStats ON dbo.MyTable(MyColumn); UPDATE STATISTICS dbo.MyTable;

2. Use proper distribution:

a. Big tables → HASH on join key.
b. Small dimensions → REPLICATE

3. Add constraints (NOT ENFORCED) for optimizer hints

ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (MyColumn) NOT ENFORCE

4. Enable Result Set Caching for repeated queries

5. Optimize Lakehouse files (if source is Delta) by using OPTIMIZE + ZORDER in Lakehouse to reduce small files

Please click 'Mark as answer' link to mark a post that answers your question, cheers.