# EDW Concepts

## MPP (massively parallel processing) Database&#x20;

* [https://www.sisense.com/glossary/mpp-database/](https://www.sisense.com/glossary/mpp-database/)

## Table Distribution Options

#### Reference

* [https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute](https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute)
* [https://techcommunity.microsoft.com/t5/datacat/choosing-hash-distributed-table-vs-round-robin-distributed-table/ba-p/305247](https://techcommunity.microsoft.com/t5/datacat/choosing-hash-distributed-table-vs-round-robin-distributed-table/ba-p/305247)
*

### Hash Distribution

#### Hash Function and Properties

* A [hash function](https://en.wikipedia.org/wiki/Hash_function) is any function that can be used to map data of arbitrary size to fixed-size
* A hash function is a mathematical function that converts a input value into another compressed numerical value.&#x20;
* The input to the hash function is of arbitrary length but output is always of fixed length.
* Properties
  1. Efficiently computable.
  2. Should uniformly distribute the keys (Each table position equally likely for each key)
* [https://www.tutorialspoint.com/cryptography/cryptography\_hash\_functions.htm](https://www.tutorialspoint.com/cryptography/cryptography_hash_functions.htm)

#### When to use Hash Function for Distribution

*

### Round Robin Distribution

### Replicated



## Data Movement

* **ShuffleMoveOperation**: Redistributes data from one distributed table to another distributed table, changing the distribution column.
* **PartitionMoveOperation**: Data moved from distributions to Control Node. Usually for Aggregations.
* **BroadcastMoveOperation**: When a distributed table needs to become replicated for join compatibility
* **TrimMoveOperation**: When a replicated table needs to become distributed
* **MoveOperationData**: Moved from Control Node back to Compute Nodes resulting in a replicated table for further processing.
* **RoundRobinMoveOperation**: Redistributes data to Round Robin Table.



## Statistics

Reference Links

* [https://www.red-gate.com/simple-talk/databases/sql-server/learn/statistics-in-sql-server/](https://www.red-gate.com/simple-talk/databases/sql-server/learn/statistics-in-sql-server/)
* [https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/#:\~:text=SQL%20Server%20statistics%20are%20essential%20for%20the%20query,cardinality%29.%20The%20query%20optimizer%20should%20be%20updated%20regularly.](https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/)
* [https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/sql-server-statistics-basics/](https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/sql-server-statistics-basics/)

## Optimization with Index

* [https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json\&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json\&view=sql-server-ver16\&preserve-view=true\&viewFallbackFrom=azure-sqldw-latest](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json\&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json\&view=sql-server-ver16\&preserve-view=true\&viewFallbackFrom=azure-sqldw-latest)

#### Reference Links

* [https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16)
*

### Clustered Column Index

Instead of storing an entire row or rows in a page, one column from many rows is stored in that page. It is this difference in architecture that gives the columnstore index a very high level of compression along with reducing the storage footprint and providing massive improvements in read performance.

Relevant Links:

* [https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/what-are-columnstore-indexes/](https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/what-are-columnstore-indexes/)
* [https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-ver16](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-ver16)
* [https://www.patrickkeisler.com/2014/04/what-is-non-clustered-columnstore-index.htm](https://www.patrickkeisler.com/2014/04/what-is-non-clustered-columnstore-index.html)
* [https://www.tutorialsteacher.com/sqlserver/indexes#:\~:text=%20Create%20a%20Clustered%20Index%20using%20Table%20Designer,table%20name%20from%20the%20File%20menu.%20More%20](https://www.tutorialsteacher.com/sqlserver/indexes)

### Heap

* [https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index](https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index)
*

### Clustered

### Secondary
