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: https://github.com/AasTrailblazers/Az... Columnstore Index https://docs.microsoft.com/en-us/sql/... https://docs.microsoft.com/en-us/azur... https://www.databasejournal.com/featu... Rowstore indexes Heap: https://docs.microsoft.com/en-us/sql/... Clustered and non-clustered: https://docs.microsoft.com/en-us/sql/... Other columnar storage technologies https://databricks.com/glossary/what-... https://cwiki.apache.org/confluence/d...