Azure Synapse Analytics | Index Options | Columnstore Index | Best Practices

Being able to create database indexes are powerful feature to improve the speed of data retrieval and to speed querying. However, same type of indexes might not be suitable for all types of workload, like transaction workload vs analytical workload.
In this video, you will learn columnstore index, how it accelerates performance of analytical queries and what are best practices and considerations when using this features.
00:00 Introduction
00:42 Rowstore indexes vs Columnstore index
04:53 Heap and when to use it
06:16 Clustered and Non-clustered Index and when to use it
07:36 Columnstore index - design and storage
08:31 Practical example of creating these types of indexes
16:18 Columnstore deep dive
18:53 Practical example of columnstore index storage
28:47 Columnstore index - updates of data
35:10 Practical example of making data updates to clustered columnstore index table
42:15 Guidelines for choosing right type of indexes
Thank you for watching, in my next video, I am going to talk in detail about table partitioning and how it helps in improving performance for analytical queries if used prudently. Stay tuned.
GitHub Repo to download deck and script used in the video:
github.com/AasTrailblazers/Az...
Columnstore Index
docs.microsoft.com/en-us/sql/...
docs.microsoft.com/en-us/azur...
www.databasejournal.com/featu...
Rowstore indexes
Heap: docs.microsoft.com/en-us/sql/...
Clustered and non-clustered: docs.microsoft.com/en-us/sql/...
Other columnar storage technologies
databricks.com/glossary/what-...
cwiki.apache.org/confluence/d...

[@amiyakumarnayak8286]

It's the most detailed and thorough explanation. Thank you

[@plearns4551]

Too good sir, pls keep going

[@datoalavista581]

Thank you for sharing !!

[@joseantoniolopezmesa6807]

This video series are so good that I am using them as reinforcement for dp-203 exam preparation. Thank you and keep up the great work.

[@amittyagi9171]

I really admire the way you explained these concepts with demos included. Thank you.

[@shaanproms2101]

Thank you for the video Arshad!

[@mayurkc]

Detailed and thorough explanation. Thank you for this series.

[@KathyLoisAmores]

Easy to follow. Thanks so much.

[@SinghalPuneet]

Best explanation on topics.. makes each bit very clear and always goes from left to right instead of jumping to/fro.

[@gudukumar748]

Content and explanation is really simple to understand...thank you sir jee. awesome to see you after long time.

[@VK-ln9vk]

wonderful video on indexes.Thank you so much sir

[@deepakganesh1033]

Thank you very much for providing such a detailed information of Azure Sql Data Warehouse

[@ArshadAliAasTrailblazers]

You are most welcome

[@sanjuaneselperu4755]

Great !! Your explanation was very good.
Now I have tables in the Synapse Pool that need statistics, a video recommending the correct execution of the statistics in tables would be very good.

[@Krishna-st4vp]

very well presented , thank you

[@ArshadAliAasTrailblazers]

You are welcome!

[@rahamanabdul6388]

very well explained..Thank you so much for great videos..

[@ArshadAliAasTrailblazers]

Thanks Rahaman for your kind words, glad it was helpful!

[@ateet82]

Thanks for taking out time and providing such a insightful details about indexing !!
Any thoughts at 40:00 - how can someone identify if particular rowgroup is softdeleted ? and why so 2 RowGroup/Distribution during ingestion & 1 RowGroup/Distribution during update ? is there any pattern or logic behind it ?

[@ArshadAliAasTrailblazers]

Thanks Ateet! A rowgroup with "Tombstone" state indicates all data is deleted. th-cam.com/video/VasYevUAStk/w-d-xo.html