In this article, we will delve into essential concepts within the domain of analytics databases, conducting a comparative analysis of the available offerings for Azure SQL databases based on these foundational principles. Let’s look at some key concepts before we delve into the data storage options in Azure.
Clustered Index
- A B-Tree clustered index organizes rows physically in memory in sorted order, automatically created when the primary key is established.
- The key advantage of a clustered index lies in the swift searching of a range of values. Internally utilizing a B-Tree data structure, the leaf node of the B-Tree clustered index contains the actual table data.
- It is important to note that only one clustered index can be created for a table.
Non-Clustered Index
- A non-clustered index also employs a B-Tree data structure, with the distinction that the leaf node of the B-Tree or non-clustered index contains pointers to the pages containing the actual table data.
- Unlike a clustered index, a non-clustered index does not organize rows physically in memory in a sorted order.
- Importantly, it is permissible to create more than one non-clustered index for a table.
Clustered Column Store
Clustered column-store storage involves organizing all data in a table in a columnar format, significantly compressing the data and facilitating rapid execution of analytical queries and reports. Depending on the data characteristics, data size may be reduced by a factor of 10x to 100x. The clustered column-store model excels in the quick ingestion of substantial data volumes (bulk-load) as large batches exceeding 100,000 rows undergo compression before storage on disk. This model is particularly well-suited for classic data warehouse scenarios.