Structure Options
Choose the option in dropdown list:
• Clustered columnctore index
• Heap
• Clustered index
• None
Clustered columnstore index
Stores the table as a clustered columnstore index. The clustered columnstore index applies to all of the table data. This behavior is the default for Azure Synapse Analytics.
Heap
Stores the table as a heap. This behavior is the default for Parallel Data Warehouse.
Clustered index
CLUSTERED INDEX ( index_column_name [ ,...n ] )
Stores the table as a clustered index with one or more key columns. This behavior stores the data by row. Use index_column_name to specify the name of one or more key columns in the index. For more information, see Rowstore Tables in the General Remarks.
Distribution Options
See dropdown list with these options:
• Round Robin
• Replicate
• Hush
• none
Hash
DISTRIBUTION = HASH( distribution_column_name ) Assigns each row to one distribution by hashing the value stored in distribution_column_name. The algorithm is deterministic, which means it always hashes the same value to the same distribution. The distribution column should be defined as NOT NULL because all rows that have NULL are assigned to the same distribution.
Round Robin
DISTRIBUTION = ROUND_ROBIN Distributes the rows evenly across all the distributions in a round-robin fashion. This behavior is the default for Azure Synapse Analytics.
Replicate
DISTRIBUTION = REPLICATE Stores one copy of the table on each Compute node. For Azure Synapse Analytics the table is stored on a distribution database on each Compute node. For Parallel Data Warehouse, the table is stored in a SQL Server filegroup that spans the Compute node. This behavior is the default for Parallel Data Warehouse.
Partition Options
Here you can see
• Select Member option
• Set Value option
• Select left or right option
PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [,...n] ] ))
Creates one or more table partitions. These partitions are horizontal table slices that allow you to apply operations to subsets of rows regardless of whether the table is stored as a heap, clustered index, or clustered columnstore index. Unlike the distribution column, table partitions don't determine the distribution where each row is stored. Instead, table partitions determine how the rows are grouped and stored within each distribution.
Member
Choose column name in the dropdown list
Value
partition_column_name | Specifies the column that Azure Synapse Analytics will use to partition the rows. This column can be any data type. Azure Synapse Analytics sorts the partition column values in ascending order. The low-to-high ordering goes from |
| Specifies the boundary value belongs to the partition on the left (lower values). The default is LEFT. |
| Specifies the boundary value belongs to the partition on the right (higher values). |
| Specifies the boundary values for the partition. boundary_value is a constant expression. It can't be NULL. It must either match or be implicitly convertible to the data type of partition_column_name. It can't be truncated during implicit conversion so that the size and scale of the value don't match the data type of partition_column_name |
Place the value in the multi-input, choose a direction (left or right) and click the "Add" button.
-- **************** SqlDBM: Azure Synapse Analytics *****************
-- ******************************************************************
-- ************************************** [Fact_Customer_Orders]
CREATE TABLE [Fact_Customer_Orders]
(
[OrderId] int NOT NULL,
[SupplierId] int NOT NULL,
[ProductId] int NOT NULL,
[Price] decimal NOT NULL,
[Quantity] int NOT NULL,
[Profit] decimal NOT NULL,
[Date] datetime NOT NULL
)
WITH (
CLUSTERED COLUMNSTORE INDEX,
DISTRIBUTION = ROUND_ROBIN,
PARTITION ( [Date] RANGE RIGHT FOR VALUES ( '2019-01-01', '2019-08-01', '2019-09-01', '2019-10-01', '2019-02-01', '2019-11-01', '2019-12-01', '2019-03-01', '2019-04-01', '2019-05-01', '2019-06-01', '2019-07-01' ) )
);
Learn more:
See also: