Creating partitions and archiving data using partitioned tables

Partitioned tables split the table into smaller segments based on a particular column’s values. Splitting the table into multiple partitions makes data loading and data archival faster and can also improve query performance. In this recipe, we will perform the following tasks:

• Creating a partitioned table and loading the data
• Archiving data older than 6 months using partition commands

Getting ready

Create a Synapse Analytics workspace as explained in the Provisioning an Azure Synapse Analytics workspace recipe of Chapter 8, Processing Data Using Azure Synapse Analytics.

Create a Synapse dedicated SQL pool named packtadesqlpool and an external table named ext_transaction_tbl, as described in steps 1 to 9 in the How to do it… section of the Loading data into dedicated SQL pools using PolyBase using T-SQL recipe. Alternatively, you can use the Create_External_Table.SQL script from https...